Write a SQL query to count the number of unique user per day who logged in from both iPhone and web

1-question.md

Question

Write a SQL query to count the number of unique user per day who logged in from both iPhone and web

Source

https://www.youtube.com/watch?v=Td-cmLfQ7uU by Tina Huang

Category

• MYSQL

2-scheme.sql

create table web (
	ts timestamp,
	userid int,
	web_sessionid int
);
create table iphone (
	ts timestamp,
	userid int,
	iphone_sessionid int
);

insert into web (ts, userid, web_sessionid)
values
(now(), 1, 100),
(now() - interval 1 hour, 1, 101),
(now() - interval 1 day, 2, 103);

insert into iphone (ts, userid, iphone_sessionid)
values
(now(), 1, 100),
(now() - interval 2 hour, 1, 101),
(now() - interval 1 day, 3, 103);

3-answer.sql

SELECT
date(combined.ts) AS day, 
count(distinct combined.userid) AS num_users 
FROM
((select * from web) UNION (select * from iphone)) as combined
group by 
1;