Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
83 lines (75 sloc) 2.71 KB
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 |
+------------+---------+----------+-----------+
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 |
+--------+