CODEWITHSUNDEEP
sqlpostgresql
Lokesh Kumar
Lokesh Kumar

Reputation: 11

Postgres: get count of users who have taken trial and taken paid subscription on weekly basis

I know the title is not helpful sorry for that. I am lost, as i don't know what i am looking for filtering data.

Here is my Subscription schema

id user_id type sub_type created_at
1 1 A trial August 1, 2021, 12:00 AM
2 2 A trial August 8, 2021, 12:00 AM
3 3 A trial August 8, 2021, 12:00 AM
4 1 A paid August 8, 2021, 12:00 AM
5 2 A paid August 8, 2021, 12:00 AM

I want to know on weekly basis how many users took trial and how many of them taken paid subscription. sort of like this

week      trial paid
August 1   1     1
August 8   2     1

I got weekly trial by this query

SELECT date_trunc('week', created_at::date) AS weekly,
       COUNT(user_id)
FROM subscription
WHERE sub_type = 'trial'
GROUP BY weekly
ORDER BY weekly;

I am new to db queries and not sure if i should be using join or sub queries. PLEASE HELP!

Upvotes: 1

Views: 122

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

I think you want conditional aggregation, which in Postgres is best done using filter:

SELECT date_trunc('week', created_at::date) AS weekly,
       COUNT(*) FILTER (WHERE sub_type = 'trial') as num_trials,
       COUNT(*) FILTER (WHERE sub_type = 'paid') as num_paid
FROM subscription
GROUP BY weekly
ORDER BY weekly;

Upvotes: 1

Related Questions