拼多多笔试题0805_统计用户数据

笔试题描述


表格构建

create table buy(user_id int,item_id int,buy_time DATE);
create table fork(user_id int,item_id int ,fork_time DATE);
insert into buy values(0001,201,'2008-09-04');
insert into buy values(0001,206,'2008-09-04');
insert into buy values(0002,203,'2008-09-04');
insert into buy values(0003,204,'2008-09-04'); insert into fork values(0001,203,'2008-09-04');
insert into fork values(0001,201,'2008-09-04');
insert into fork values(0001,205,'2008-09-04');
insert into fork values(0004,203,'2008-09-04');
insert into fork values(0003,204,'2008-09-04');
insert into fork values(0002,201,'2008-09-04');

表格结果如下:

数据观察

有些商品已购买,未收藏

有些商品未购买,已收藏


题目分析

一、合并表格

SELECT *
FROM buy LEFT JOIN fork
ON buy.user_id=fork.user_id AND buy.item_id=fork.item_id;

表格结果如下:

SQL练习题_用户购买收藏记录合并(拼多多)-LMLPHP


二、CASE表示(0,1)

CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'

最后结果及代码

SELECT buy.user_id,buy.item_id,
CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'
FROM buy LEFT JOIN fork
ON buy.user_id=fork.user_id and buy.item_id=fork.item_id

SQL练习题_用户购买收藏记录合并(拼多多)-LMLPHP


三、同理复制FORK表

SELECT fork.user_id,fork.item_id,
CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'
FROM fork LEFT JOIN buy
ON buy.user_id=fork.user_id and buy.item_id=fork.item_id

SQL练习题_用户购买收藏记录合并(拼多多)-LMLPHP


题目解答

SELECT buy.user_id,buy.item_id,
CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'
FROM buy LEFT JOIN fork
ON buy.user_id=fork.user_id and buy.item_id=fork.item_id
UNION
SELECT fork.user_id,fork.item_id,
CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'
FROM fork LEFT JOIN buy
ON buy.user_id=fork.user_id and buy.item_id=fork.item_id
ORDER BY user_id,item_id;

SQL练习题_用户购买收藏记录合并(拼多多)-LMLPHP

05-27 18:20