SQL: Count the COUNT() of occurrences of a value each day

Question

So i have a table of Subscription Cancellations that has the following schema: USER_ID, CANCEL_TYPE, OCCURRED_AT.

CANCEL_TYPE can take a range of values: "Cancelled Free Trial", "Cancelled Paying", "Set Cancellation".

I want to group by OCCURRED_AT and CANCEL_TYPE to get a COUNT(USER_ID) for each cancel_type on each day - basically giving the total number of occurrences of each type of cancellation on each day.

Here's my statement at the moment:

select
    extract(date from occurred_at) as date, cancel_type, COUNT(customer_id) as total
from TABLE
group by date, cancel_type
order by date, cancel_type

Problem is, some days nobody cancels a subscription or not all cancel_types occur. How can I edit this statement to return all cancel_types for all dates with 0 or NULL values if they don't occur?

Answer 1

You can have 2 CTE on top for cancel types and Dates. And then cross join Dates to Cancel types and left join this to your table. You can change order by and group by as you require

WITH Dates AS (
    SELECT DISTINCT DATE_TRUNC('DAY', occurred_at) AS date
    FROM TABLE),
 
CancelTypes AS (
    SELECT DISTINCT cancel_type
    FROM TABLE)

SELECT
    d.date,
    ct.cancel_type,
    COALESCE(COUNT(t.customer_id), 0) AS total
FROM
    Dates d
CROSS JOIN
    CancelTypes ct
LEFT JOIN
    TABLE t
    ON d.date = DATE_TRUNC('DAY', t.occurred_at)
    AND ct.cancel_type = t.cancel_type
GROUP BY
    d.date, ct.cancel_type
ORDER BY
    d.date, ct.cancel_type;