一大波SQL面试题+solution+转换成Python/R的写法

Q1 sql 的正确解法



;with scope
as
(
select
'both' as channel,
customer_id,
date,
sum(spend) as Sum_2,
iif(count(channel)<2,0,count(channel)) as tot_channel
from dbo.spend
group by customer_id,
date
),Isolate
as(
select
date,
channel,
max(iif(tot_channel <2,0, sum_2)) as tot_spend,
max(tot_channel) as count1

from scope
group by date, channel

),FINAL
as(
select * from isolate
union
select
date,
channel,
sum(spend) as tot_spend,
count(customer_id) as count1
from dbo.spend
group by date, channel,customer_id
)

select
*
from FINAL
order by date, case when channel ='both' then'9'
                                        when channel = 'mobile' then '1'
                                        when channel = 'destop' then '2'
                                end ASC
                                       

Make马克~感谢楼主！

Q2 in Mysql

CREATE VIEW career AS
SELECT member_id, group_concat(company_name ORDER BY year_start SEPARATOR '-') AS member_career
FROM job
GROUP BY member_id;
# 1)
SELECT count(*)
FROM career
WHERE member_career LIKE '%Microsoft%Google%';
# 2)
SELECT count(*)
FROM career
WHERE member_career LIKE '%MicrosoftGoogle%';

谢谢楼主分享！！！！

谢谢楼主分享，学习了

谢谢楼主分享！

感谢楼主分享～手动点赞～

Thanks for sharing!

赞！谢谢分享！

超级有用，谢谢