Link to Github
DDL Scripts
QUESTIONS
1)SELECT DISTINCT EVENT IN THE TABLE
2) GET THE NO OF USERS WHO SENT THE MESSAGES IN OCT Month of oct how many users who have sent messages;
3)GET THE DISTINCT COUNT OF USERS
4)GET THE DISTINCT COUNT OF USERS PER DAY
5)PERCENTAGE OF USER ACCEPTED THE FRIENDS REQUEST IN THE MONTH OF OCT Total of sent sum (accepted)/total sent
DDL Scripts
create table table1(time date,user_id int,app varchar(40),event varchar(40)); insert into table1 values('2018-10-01',1,'facebook','processed'); insert into table1 values('2018-10-01',1,'facebook','sent'); insert into table1 values('2018-10-01',1,'facebook','accepted'); insert into table1 values('2018-10-01',2,'facebook','sent'); insert into table1 values('2018-10-02',2,'facebook','accepted'); insert into table1 values('2018-10-01',3,'facebook','sent'); select * from table1; +------------+---------+----------+-----------+ | time | user_id | app | event | +------------+---------+----------+-----------+ | 2018-10-01 | 1 | facebook | processed | | 2018-10-01 | 1 | facebook | sent | | 2018-10-01 | 1 | facebook | accepted | | 2018-10-01 | 2 | facebook | sent | | 2018-10-02 | 2 | facebook | accepted | | 2018-10-01 | 3 | facebook | sent | +------------+---------+----------+-----------+
QUESTIONS
1)SELECT DISTINCT EVENT IN THE TABLE
select distinct(event) from table1; +-----------+ | event | +-----------+ | processed | | sent | | accepted | +-----------+
2) GET THE NO OF USERS WHO SENT THE MESSAGES IN OCT Month of oct how many users who have sent messages;
select user_id from table1 where event='sent' and time between '2018-10-01' and '2018-10-31'; +---------+ | user_id | +---------+ | 1 | | 2 | | 3 | +---------+
3)GET THE DISTINCT COUNT OF USERS
select count(distinct user_id) as no_users from table1 where timebetween '2018-10-01' and '2018-10-31'; +----------+ | no_users | +----------+ | 3 | +----------+
4)GET THE DISTINCT COUNT OF USERS PER DAY
select time,count(distinct user_id) as no_users from table1 where time between '2018-10-01' and '2018-10-31' group by time; +------------+----------+ | time | no_users | +------------+----------+ | 2018-10-01 | 3 | | 2018-10-02 | 1 | +------------+----------+
5)PERCENTAGE OF USER ACCEPTED THE FRIENDS REQUEST IN THE MONTH OF OCT Total of sent sum (accepted)/total sent
select a.total/b.sent as ratio from (select count(event) as total from table1 where event='accepted' ) a , ( select count(event) as sent fromtable1
where event='sent') b; +--------+ | ratio | +--------+ | 0.6667 | +--------+
No comments:
Post a Comment