SQL刷题

SQL入门题：

Day07~Day08 计算函数

18、1693. 每天的领导和合伙人

题目： 表：dailysales

该表没有主键。该表包含日期、产品的名称，以及售给的领导和合伙人的编号。名称只包含小写英文字母。

写一条 SQL 语句，使得对于每一个 date_id 和 make_name，返回不同的 lead_id 以及不同的 partner_id 的数量。

按 任意顺序 返回结果表。查询结果格式如下示例所示。

示例1：输入：DailySales表：

输出：

解释：
在 2020-12-8，丰田（toyota）有领导者 = [0, 1] 和合伙人 = [0, 1, 2] ，同时本田（honda）有领导者 = [1, 2] 和合伙人 = [1, 2]。
在 2020-12-7，丰田（toyota）有领导者 = [0] 和合伙人 = [1, 2] ，同时本田（honda）有领导者 = [0, 1, 2] 和合伙人 = [1, 2]。

解答： count(distinct col) 计算非重复结果的数目

select date_id, make_name, count(distinct lead_id), count(distinct partner_id)
    from dailysales
    group by date_id,make_name

19、1141. 查询近30天活跃用户数

题目： 活动记录表：activity

该表是用户在社交网站的活动记录。该表没有主键，可能包含重复数据。activity_type 字段为以下四种值 ('open_session', 'end_session', 'scroll_down', 'send_message')。
每个 session_id 只属于一个用户。

请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。以 任意顺序 返回结果表。

解答：

方法一：

select activity_date as day, count(distinct user_id) as active_users
from activity
where datediff('2019-07-27',activity_date)<30
group by activity_date;

20、586. 订单最多的客户

题目： 表：orders

Order_number是该表的主键。此表包含关于订单ID和客户ID的信息。编写一个SQL查询，为下了 最多订单 的客户查找 customer_number 。测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。

解答：

方法一： 1、考虑并列时如下嵌套2、不考虑并列取其子查询

select customer_number 
    from orders 
        group by customer_number 
        having count(*)=(
            select count(customer_number) 
                from orders 
                    group by customer_number 
                    order by count(customer_number) desc
                    limit 1);

方法二： 窗口函数 dense_rank()

select customer_number from (
	select customer_number,
    	dense_rank() over(order by count(*) desc) as rn
    	from orders group by customer_number
)temp where rn = 1;

Day09 控制流

21、1393. 股票的资本损益

题目： 表：stocks

(stock_name, day) 是这张表的主键。operation 列使用的是一种枚举类型，包括：('Sell','Buy')。此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。保证股票的每次'Sell'操作前，都有相应的'Buy'操作。编写一个SQL查询来报告每支股票的资本损益。股票的资本损益是一次或多次买卖股票后的全部收益或损失。以任意顺序返回结果即可。

解答：

方法一： sum( case when then else end )

select stock_name, sum(case when operation = 'sell' then price else -price end) as capital_gain_loss
    from stocks
        group by stock_name

方法二：

select t1.stock_name, total_sell - total_buy as capital_gain_loss
	from (
    	(select stock_name, sum(price) total_buy
        	from stocks
        	where operation = 'buy'
        	group by stock_name) t1
        join
        (select stock_name, sum(price) total_sell
        	from stocks
        	where operation = 'sell'
        	group by stock_name) t2
        on t1.stock_name = t2.stock_name
    )
    group by stock_name

方法三： 开创函数找每次买入的最近一次卖出

select stock_name, sum(sell_price - buy_price) capital_gain_loss
	from (
    	select s1.stock_name, s1.price buy_price, s2.price sell_price,
        	row_number() over(partition by s1.stock_name, s1.operation_day order by s2.operation_day asc) rn
        from stocks s1
        left join stocks s2
        on s1.stock_name = s2.stock_name and s1.operation_day < s2.operation_day
        where s1.operation = 'buy' and s2.operation = 'sell'
    ) T
    where rn = 1
    group by stock_name

22、1407. 排名靠前的旅行者