CREATE table activity(
user_id int,
event_name varchar(20),
event_date date,
country varchar(20)
);
insert into activity values
(1,'app-installed','2022-01-01', 'India') ,
(1,'app-purchase','2022-01-02','India'),
(2,'app-installed','2022-01-01','USA'),
(3,'app-installed','2022-01-01','USA'),
(3,'app-purchase','2022-01-03','USA'),
(4,'app-installed', '2022-01-03','India'),
(4,'app-purchase', '2022-01-03','India'),
(5,'app-installed','2022-01-03','SL'),
(5,'app-purchase','2022-01-03','SL'),
(6,'app-installed','2022-01-04','Pakistan') ,
(6,'app-purchase','2022-01-04','Pakistan');
with cte as(
select user_id, event_date, count(distinct event_name) as cnt from activity
group by event_date,user_id)
select event_date, count(distinct user_id) from cte
where cnt>1
group by event_date
union
select event_date, 0 from cte
where cnt=1
group by event_date;
/*3*/
with cte as(select *,case
when country ='india' then 'india'
when country ='usa' then 'usa'
else 'other' end countr from activity)
select countr, cast(count(distinct user_id) as float) /(select count(event_name) from cte where event_name='app-purchase') *100
from cte
where event_name='app-purchase'
group by countr;
/* short form*/
with cte as (
select case
when country in('india', 'usa') then country else 'other' end countr, count(*) cnt from activity
where event_name='app-purchase'
group by case
when country in('india', 'usa') then country else 'other' end
)
select countr, cast(cnt as float)/(select sum(cnt) from cte)*100 from cte;
select a.*, b.* from activity a
right join
activity b
on
datediff(day,a.event_date, b.event_date)=1
and a.event_name<> b.event_name
and a.user_id=b.user_id
Telegram: DB developers channel