CODEWITHSUNDEEP
sqlpostgresql
Wboy
Wboy

Reputation: 2542

Postgres Query - find Difference between beginning and end of week, weekly

I have a column user_report with fields

total_user_count, sent_at

Which is sent daily.

I'm trying to find the total new users per week, which is defined as the

difference in user count from the beginning of the week to the ending of the week.

I seem to be having trouble formulating this query though, what I have is

SELECT DATE_TRUNC('day', sent_at AT TIME ZONE '+08') as date, total_user_count
FROM user_state_report
WHERE timestamptz_cmp(DATE_TRUNC('week', sent_at AT TIME ZONE '+08') , DATE_TRUNC('day', sent_at AT TIME ZONE '+08'))=0
ORDER BY TIMESTAMP

Which gives me the total user count per week. But I want the weekly new signups, not the total count

Ideal output:

Date [monday of the week X], users registered in this week 14-08-2017 300 21-08-2017 400

Thank you! :)

Upvotes: 1

Views: 93

Answers (1)

Jonathan Willcock
Jonathan Willcock

Reputation: 5235

You should be able to do this with the lag function. Something like:

SELECT date, total_user_count - COALESCE(LAG(total_user_count) OVER(order by date),0) AS new_users_registered from
(SELECT DATE_TRUNC('day', sent_at AT TIME ZONE '+08') as date, total_user_count
FROM user_state_report
WHERE timestamptz_cmp(DATE_TRUNC('week', sent_at AT TIME ZONE '+08') , DATE_TRUNC('day', sent_at AT TIME ZONE '+08'))=0
ORDER BY sent_at) l

BTW I changed your ORDER BY TIMESTAMP to ORDER BY sent_at, since I did not undertstand how your one worked!

Upvotes: 1

Related Questions