CODEWITHSUNDEEP
sqlpostgresql
Saranya
Saranya

Reputation: 756

postgreSQL- Count for value between previous month start date and end date

I have a table as follows

user_id   date         month  year   visiting_id
 123      11-04-2017   APRIL  2017   4500
 123      12-05-2017   MAY    2017   4567
 123      13-05-2017   MAY    2017   4568
 123      17-05-2017   MAY    2017   4569
 123      22-05-2017   MAY    2017   4570
 123      11-06-2017   JUNE   2017   4571
 123      12-06-2017   JUNE   2017   4572

I want to calculate the visiting count for the current month and last month at the monthly level as follows:

user_id   month   year   visit_count_this_month  visit_count_last_month
 123      APRIL   2017               1                      0
 123      MAY     2017               4                      1
 123      JUNE    2017               2                      4

I was able to calculate visit_count_this_month using the following query

SELECT v.user_id, v.month, v.year, 
       SUM(is_visit_this_month) as visit_count_this_month
FROM
   (SELECT user_id, date, month, year,
        CASE WHEN  TO_CHAR(date, 'MM/YYYY') = TO_CHAR(date, 'MM/YYYY') 
        THEN 1 ELSE 0 
        END as is_visit_this_month
   FROM visits
   GROUP BY user_id, date, month, year
   HAVING user_id = 123) v 
GROUP BY v.user_id, v.month, v.year

However, I'm stuck with calculating visit_count_last_month. Similar to this, I also want to calculate visit_count_last_2months.

Can somebody help?

Upvotes: 0

Views: 1081

Answers (2)

Brady Holt
Brady Holt

Reputation: 2924

You can use a LATERAL JOIN like this:

SELECT user_id, month, year, COUNT(*) as visit_count_this_month, visit_count_last_month
FROM visits v
    CROSS JOIN LATERAL (
        SELECT COUNT(*) as visit_count_last_month
        FROM visits
        WHERE user_id = v.user_id
            AND date = (CAST(v.date AS date) - interval '1 month')
  ) l
GROUP BY user_id, month, year, visit_count_last_month;

SQLFiddle - http://sqlfiddle.com/#!15/393c8/2

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Assuming there are values for every month, you can get the counts per month first and use lag to get the previous month's values per user.

SELECT T.*
,COALESCE(LAG(visits,1) OVER(PARTITION BY USER_ID ORDER BY year,mth),0) as last_month_visits
,COALESCE(LAG(visits,2) OVER(PARTITION BY USER_ID ORDER BY year,mth),0) as last_2_month_visits
FROM (
SELECT user_id, extract(month from date) as mth, year, COUNT(*) as visits
FROM visits
GROUP BY user_id, extract(month from date), year
) T

If there can be missing months, it is best to generate all months within a specified timeframe and left join ing the table on to that. (This example shows it for all the months in 2017).

select user_id,yr,mth,visits
,coalesce(lag(visits,1) over(PARTITION BY USER_ID ORDER BY yr,mth),0) as last_month_visits
,coalesce(lag(visits,2) OVER(PARTITION BY USER_ID ORDER BY yr,mth),0) as last_2_month_visits
from (select u.user_id,extract(year from d.dt) as yr, extract(month from d.dt) as mth,count(v.visiting_id) as visits
      from generate_series(date '2017-01-01', date '2017-12-31',interval '1 month') d(dt)
      cross join (select distinct user_id from visits) u
      left join visits v on extract(month from v.dt)=extract(month from d.dt) and extract(year from v.dt)=extract(year from d.dt) and u.user_id=v.user_id
      group by u.user_id,extract(year from d.dt), extract(month from d.dt)
      ) t

Upvotes: 1

Related Questions