Wednesday, January 9, 2019

SQL question challenge (Friends)

Link to Github

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