一亩三分地

 找回密码 注册账号

扫描二维码登录本站

微信公众号
扫码关注公众号
留学申请号
扫码关注留学申请公众号
查看: 832|回复: 16
收起左侧

问一道SQL 题,求解答

[复制链接] |只看干货 |分析|数据科学类, 面试经验, 数科面经
地里的匿名用户
地里的匿名用户  发表于 2020-11-24 22:56:19 |阅读模式
本楼: 👍   0% (0)
 
 
0% (0)   👎

2021(10-12月) 分析|数据科学类 本科 其他@ - Other - Onsite  | Other | 其他

注册一亩三分地论坛,查看更多干货!

您需要 登录 才可以下载或查看,没有帐号?注册账号

x

问大家一道leetcode变形题,求解答??

given table A:
+---------+------------+----------+--------+
| user_id | date       | platform | amount |
+---------+------------+----------+--------+
| 1       | 2019-07-01 | mobile   | 100    |
| 1       | 2019-07-01 | desktop  | 100    |
| 2       | 2019-07-01 | mobile   | 300    |
| 2       | 2019-07-02 | mobile   | 100    |. 1point3acres
| 3       | 2019-07-01 | desktop  | 50     |
| 3       | 2019-07-02 | des
游客,本帖隐藏的内容需要积分高于 188 才可浏览,您当前积分为 0。
查看如何攒积分 Click here for more info.
7-01 | desktop  | 2           |
| 2019-07-01 | mobile   | 3           |
| 2019-07-01 | both     | 1           |

上一篇:钉有趣 DA 过经
下一篇:LinkedIn DS Intern HR面
我的人缘0

升级   94.5%

本楼: 👍   100% (1)
 
 
0% (0)   👎
全局: 👍   99% (165)
 
 
0% (1)    👎
主要还是self join, 然后不用union应该可以

select date, platform2, count(user) from
(
SELECT distinct a.user, a.date,
case when a.platform!=b.platform then 'both' else a.platform end as platform2. From 1point 3acres bbs
FROM table a join table b
on a.user=b.user and a.date=b.date
)x
group by date, platform2
order by date, platform2
回复

使用道具 举报

我的人缘0

升级   0.75%

本楼: 👍   100% (1)
 
 
0% (0)   👎
全局: 👍   97% (860)
 
 
2% (26)    👎
本帖最后由 tiffany345 于 2020-11-24 18:22 编辑

我这里用了两个CTE
第一个里面先 left join 来判断 platform
第二个里面用了 union 来合并所有的可能性

根据 test case 提供的数据可以在 MySQL 里面得到如下结果:


[SQL] 纯文本查看 复制代码
WITH CTE1 as   (
SELECT DISTINCT
a.user_id,
a.date,
case 
when a.platform = 'mobile' and b.platform is null then 'mobile'
when a.platform = 'desktop' and b.platform is null then 'desktop'
when a.platform is not null and b.platform is not null then 'both'
else null end as 'platform'

FROM platform a LEFT JOIN platform b
ON a.user_id = b.user_id 
AND a.date = b.date
AND a.platform <> b.platform
),

CTE2 as   (
select user_id, date, platform from CTE1
union
select user_id, date, 'mobile' as platform from CTE1 where platform = 'both'
union
select user_id, date, 'desktop' as platform from CTE1 where platform = 'both'
)


SELECT 
date,
platform,
count(user_id) as total_users

FROM CTE2
GROUP BY date, platform
ORDER BY date, platform desc
;



本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?注册账号

x
回复

使用道具 举报

我的人缘0

升级   11%

本楼: 👍   100% (1)
 
 
0% (0)   👎
全局: 👍   84% (42)
 
 
16% (8)    👎
方法比较笨,但是应该有效

[SQL] 纯文本查看 复制代码
with a as (
select date, user_id, sum(case when platform='mobile' then amount else 0 end ) as ma,sum(case when platform='desktop' then amount else 0 end) as da from A group by date, user),
ref as (select distinct date, 'mobile' as platform from A union (select distinct date,'desktop' as platform from A) union select distinct date,'both' as platform from A),
b as (select date, count(case when ma>0 and da>0 then userid else null end) as bothn, count(case when ma=0 and da>0 then userid else null end) as dn, count(case when da=0 and ma>0 then userid else null end) as mn from a group by date)
select date, platform, coalesce(total_users, 0) as total_users from (
select date, 'both' as p , bothn as total_users from b where bothn >0 union select date, 'mobile' as p, mn as total_users from b where mn>0 union select date,  'desktop' as p ,dn as total_users from b where dn>0) sub right join ref on sub.date=ref.date,sub.p=ref.platform
回复

使用道具 举报

我的人缘0

升级   13.13%

本楼: 👍   0% (0)
 
 
100% (1)   👎
全局: 👍   97% (287)
 
 
2% (6)    👎
combine rows,要用for, 再用一个case when
回复

使用道具 举报

我的人缘0

升级   13.5%

本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (38)
 
 
0% (0)    👎
Group by date, count user id. Having date = 2019-7-1

评分

参与人数 1大米 +1 收起 理由
charlie029 + 1 给你点个赞!

查看全部评分

回复

使用道具 举报

我的人缘0
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (49)
 
 
0% (0)    👎
cloverlyy 发表于 2020-11-25 01:36
Group by date, count user id. Having date = 2019-7-1

这题最终结果应该不只2019-07-01的
回复

使用道具 举报

我的人缘0

升级   13.13%

本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   97% (287)
 
 
2% (6)    👎
踩我的朋友,要是有5个platform 你会scale吗?不写for你来做一个?count(userid) = 2你能知道是哪2个platform?莫名其妙
回复

使用道具 举报

我的人缘0

升级   13.5%

本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (38)
 
 
0% (0)    👎
Qomo 发表于 2020-11-25 02:19
这题最终结果应该不只2019-07-01的

嗯嗯嗯,我的答案不对。 积分不够,看不了全文。不知道答案中both是什么。。。
回复

使用道具 举报

我的人缘0
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (49)
 
 
0% (0)    👎
cloverlyy 发表于 2020-11-25 04:02
嗯嗯嗯,我的答案不对。 积分不够,看不了全文。不知道答案中both是什么。。。

both 应该是指 each user_id 既有 mobile transaction 也有 desktop transaction.
回复

使用道具 举报

我的人缘0

升级   23%

本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (54)
 
 
0% (0)    👎
能想到的最简单的方法是用union。desktop和mobile的用group by就可以,后面both的可以self join一下date+user_id并且左边取desktop右边取mobile,两个都不是null就是1,然后sum一下,最后两个table union all起来。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册账号

本版积分规则

隐私提醒:
■拉群请前往同学同事飞友|拉群结伴版块,其他版块拉群,帖子会被自动删除
■论坛不能删帖,为防止被骚扰甚至人肉,不要公开留微信等联系方式,请以论坛私信方式发送。
■特定版块可以超级匿名:https://tools.1point3acres.com/thread
■其他版块匿名方法:http://www.1point3acres.com/bbs/thread-405991-1-1.html

手机版|||一亩三分地

Powered by Discuz! X3

© 2001-2013 Comsenz Inc. Design By HUXTeam

Some icons made by Freepik from flaticon.com

快速回复 返回顶部 返回列表