select current_month, count(distinct user_id) from (select user_id, extract(month from event_date) as current_month, case when extract(month from event_date) - coalesce(lag(extract(month from event_date)) over(partition by user_id order by extract(month from event_date)), -1) = 1 then 'Active' else 'Inactive' end as user_status from user_actions where event_type in ('like', 'comment') ) a where user_status = 'Active' group by current_month

with cte as (select month(event_date) event,user_id,event_type from user_actions where event_type in('like','comment') group by user_id,month(event_date),event_type), cte2 as (select *,lag(event,1,event) over (partition by user_id order by event) as lag_month from cte), cte3 as (select *,(event-lag_month) sds from cte2) select event,sum(sds) as user_count from cte3 group by event having sum(sds) 0

@datasculptor2895

Ай бұрын

Solution works but very complex. Can be handled using simple joins. Please watch the video till the end.

Mysql solution : with cte as( select user_id,event_id, month(event_date)as month, event_type from user_actions where event_type in ('like','comment')) select t1.user_id,t1.event_id ,t1.event_type,t1.month,t2.month from cte t1 join cte t2 on t1.user_id = t2.user_id and t1.month = t2.month-1

@Rex-Solis

19 күн бұрын

why did we did t2.month - 1 and not +1 , as the month is increased by one from may to june then why subtract plz help

@kadagaladurgesh3691

14 күн бұрын

@@Rex-Solis To satisfy the condition (t1.month = t2.month -1) ex: t1.month = 5 t2.month = 6 t1.month = t2.month -1) => (5 = 6-1) => (5=5) result : user_id | event_id | event_type | month_1 | month_2 | event_type | event_id | +---------+----------+------------+-------+-------+------------+----------+ | 445 | 7765 | like | 5 | 6 | comment | 7767 | if you use (t1.month = t2.month +1) t1.month = 6 t2.month = 5 t1.month = t2.month +1) => (6 = 5+1) => (6=6) then result : user_id | event_id | event_type | month_1 | month_2 | event_type | event_id | +---------+----------+------------+-------+-------+------------+----------+ | 445 | 7767 | comment | 6 | 5 | like | 7765 |

Hi im try this code Slect * from user-actions a Innser join user-actions b On a.user-id=b.user-id And month(a.event_date)-(b.event_date) Order by a.user_id; Here shows invalid relarional opreator

@user-qs9yn2hb4b

Ай бұрын

Please help me to resolve the error

@datasculptor2895

Ай бұрын

month(a.event_date) - month(b.event_date) =1