'PostgreSQL: COUNT, GROUP and MIN date_part data to get first interaction
I have a list of emails and the dates of interaction over multiple campaigns, I'm attempting to consolidate these dates by year and find the first interaction by an email and which year it fell in.
SELECT date_part('year', MIN(x.created_at)) as years
FROM (
SELECT email, created_at FROM s_subscribers_491 UNION ALL
SELECT email, created_at FROM s_subscribers_493 UNION ALL
SELECT email, created_at FROM s_subscribers_1190
) x
GROUP BY x.email
The above query provides the results of the first year of interaction for each email, however I need to group these by year and then count how many by year, however I am running in to issues GROUPING BY date_part('year', MIN(x.created_at)) or even using the alias.
Results I am receiving from the above query:
years
1 2018
2 2018
3 2020
4 2020
5 2018
6 2021
7 2017
8 2020
9 2018
Desired Outcome:
years count
1 2017 1
2 2018 4
3 2020 3
4 2021 1
Many thanks
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
