CODEWITHSUNDEEP
sql
Ravi
Ravi

Reputation: 667

Week over week change of status using SQL

I am trying to write a SQL which will give me the count of users who's status changed over the period of week over week.

For e.g. User_id 1 has a status of 1 on week_end_dt of '2020-01-04' and then the status changed to 0 for the next week_end_dt. So for the first week all the 4 user's had status 1, hence the count would be 4. For the next week, except for user 3 rest changed their status, hence the count would be 1. So on and so forth.

Output

week_end_dt  count_changed_0_1    count_change_1_0
2020-01-04        4                        0
2020-01-11        1                        0
2020-01-18        1                        1

The last row (above) is because user 2 changed from 0 to 1 while user 3 changed from 1 to 0.

create table user_status (user_id int, week_end_dt date, status int);

insert into user_status values (1,'2020-01-04', 1);
insert into user_status values (1,'2020-01-11', 0);
insert into user_status values (1,'2020-01-18', 0);
insert into user_status values (1,'2020-01-25', 0);
insert into user_status values (1,'2020-02-01', 0);

insert into user_status values (2,'2020-01-04', 1);
insert into user_status values (2,'2020-01-11', 0);
insert into user_status values (2,'2020-01-18', 1);
insert into user_status values (2,'2020-01-25', 0);
insert into user_status values (2,'2020-02-01', 0);

insert into user_status values (3,'2020-01-04', 1);
insert into user_status values (3,'2020-01-11', 1);
insert into user_status values (3,'2020-01-18', 0);
insert into user_status values (3,'2020-01-25', 1);
insert into user_status values (3,'2020-02-01', 0);
insert into user_status values (3,'2020-02-08', 1);
insert into user_status values (3,'2020-02-15', 0);
insert into user_status values (3,'2020-02-22', 1);

insert into user_status values (4,'2020-01-04', 1);
insert into user_status values (4,'2020-01-11', 0);
insert into user_status values (4,'2020-01-18', 0);
insert into user_status values (4,'2020-01-25', 0);
insert into user_status values (4,'2020-02-01', 1);

Upvotes: 0

Views: 233

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Assuming you have data for every week (no gaps) as in your sample data, you can use lag() and aggregation like this:

select week_end_dt,
       sum(case when prev_status = status then 0 else 1 end) as num_changes
from (select us.*, lag(status) over (partition by user_id order by week_end_dt) as prev_status
      from user_status us
     ) us
group by week_end_dt
order by week_end_dt;

Here is a db<>fiddle.

Upvotes: 2

Related Questions