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

CodePudding user response：

Just nest your query inside yet another sub-query. That allows you to do two distinct levels of aggregations...

SELECT
  years,
  COUNT(*)
FROM
(
  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
  )
    AS subscribers
  GROUP BY
    email
)
  AS firstSubscription
GROUP BY
  years

Though, I'd use CTEs to lay it out more 'linearly'...

WITH
  subscribers AS
(
  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
),
  firstSubscription AS
(
  SELECT
    date_part('year', MIN(x.created_at)) as years
  FROM
    subscribers
  GROUP BY
    email
)
SELECT
  years,
  COUNT(*)
FROM
  firstSubscription
GROUP BY
  years