Skip to content

Instantly share code, notes, and snippets.

@JunichiIto JunichiIto/answer.sql
Last active Feb 6, 2017

Embed
What would you like to do?
【SQL腕試し問題!】入会者数と退会者数を日付ごとに集計するSQLを書いてくださいの解答例
-- http://qiita.com/jnchito/items/1d21fa3970b3c76bee43
WITH target_dates AS
(SELECT joined_on AS target_date
FROM users
UNION
SELECT left_on AS target_date
FROM users
WHERE left_on IS NOT NULL ),
joined_counts AS
(SELECT joined_on,
count(*) AS joined_count
FROM users
GROUP BY joined_on ),
left_counts AS
(SELECT left_on,
count(*) AS left_count
FROM users
WHERE left_on IS NOT NULL
GROUP BY left_on )
SELECT TO_CHAR(td.target_date, 'YYYY-MM-DD') AS date,
COALESCE(j.joined_count, 0) AS joined_count,
COALESCE(l.left_count, 0) AS left_count
FROM target_dates td
LEFT OUTER JOIN joined_counts j ON j.joined_on = td.target_date
LEFT OUTER JOIN left_counts l ON l.left_on = td.target_date
ORDER BY td.target_date;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.