大厂真实面试题(二)

小红书大数据面试SQL-用户商品购买收藏行为特征加工

1.题目

已知有
购买记录表t_order,包含自增id:id,用户ID:user_id，商品ID:goods_id,订单时间：order_time,商品类别：goods_type;
用户收藏记录表t_collect_log,包含自增id，用户ID:user_id，商品ID：goods_id，收藏时间 collect_time
请用一句sql语句得出以下查询结果，得到所有用户的商品行为特征，其中用户行为分类为4种：是否已购买、购买未收藏、收藏未购买、收藏且购买。
购买记录表t_order

收藏记录表t_collect_log

期望结果

2. 分析

因为要分析收藏与购买的关系，首先要对订单表进行处理保留订单用户和good_id，同样的对收藏表也进行处理，然后对两个表进行full join。先把所有的user_id 和 goods_id取出来，然后进行特征加工：
是否购买： 根据 t_ord中的g