面试真题
腾讯
4.连续5天涨幅超过5%的股票

腾讯大数据面试SQL-连续5天涨幅超过5%的股票

一、题目

现有一张股票价格表t4_stock_data有3个字段分别是股票代码(stock_code),日期(trade_date)，收盘价格(closing_price) ，请找出满足连续5天以上（含）每天上涨超过5%的股票,并给出连续满足天数及开始和结束日期。 备注：不考虑停牌或其他情况，仅仅关注每天连续5天上涨超过5%的股票。

样例数据

+-------------+-------------+----------------+
| stock_code  | trade_date  | closing_price  |
+-------------+-------------+----------------+
| AAPL        | 2023-02-26  | 100.00         |
| AAPL        | 2023-02-27  | 105.00         |
| AAPL        | 2023-02-28  | 110.25         |
| AAPL        | 2023-03-01  | 115.78         |
| AAPL        | 2023-03-02  | 121.59         |
| AAPL        | 2023-03-03  | 128.73         |
| AAPL        | 2023-03-04  | 137.00         |
| AAPL        | 2023-03-05  | 144.67         |
| AAPL        | 2023-03-06  | 147.64         |
| GOOG        | 2023-02-26  | 2000.00        |
| GOOG        | 2023-02-27  | 2100.00        |
| GOOG        | 2023-02-28  | 2205.00        |
| GOOG        | 2023-03-01  | 2313.25        |
| GOOG        | 2023-03-02  | 2431.01        |
| GOOG        | 2023-03-03  | 2547.56        |
| GOOG        | 2023-03-04  | 2680.19        |
| GOOG        | 2023-03-05  | 2814.20        |
| GOOG        | 2023-03-06  | 2955.91        |
+-------------+-------------+----------------+

二、分析

首先应该计算每天相对昨天的涨幅，这个使用lag函数； 然后判断是否符合涨幅超过5%； 然后处理连续问题，使用row_number()函数； 最后计算天数、开始日期、结束日期，返回结果；

维度评分
题目难度⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️⭐️

三、SQL

1.使用lag函数计算每天的涨幅。

执行SQL

SELECT stock_code,
       trade_date,
       closing_price / LAG(closing_price) OVER (PARTITION BY stock_code ORDER BY trade_date ASC) - 1 AS daily_return
FROM t4_stock_data;

执行结果

+-------------+-------------+------------------+
| stock_code  | trade_date  |   daily_return   |
+-------------+-------------+------------------+
| AAPL        | 2023-02-26  | NULL             |
| AAPL        | 2023-02-27  | 0.0500000000000  |
| AAPL        | 2023-02-28  | 0.0500000000000  |
| AAPL        | 2023-03-01  | 0.0501587301587  |
| AAPL        | 2023-03-02  | 0.0501813784764  |
| AAPL        | 2023-03-03  | 0.0587219343696  |
| AAPL        | 2023-03-04  | 0.0642429892022  |
| AAPL        | 2023-03-05  | 0.0559854014599  |
| AAPL        | 2023-03-06  | 0.0205294808875  |
| GOOG        | 2023-02-26  | NULL             |
| GOOG        | 2023-02-27  | 0.0500000000000  |
| GOOG        | 2023-02-28  | 0.0500000000000  |
| GOOG        | 2023-03-01  | 0.0490929705215  |
| GOOG        | 2023-03-02  | 0.0509067329515  |
| GOOG        | 2023-03-03  | 0.0479430360221  |
| GOOG        | 2023-03-04  | 0.0520615804927  |
| GOOG        | 2023-03-05  | 0.0500001865539  |
| GOOG        | 2023-03-06  | 0.0503553407718  |
+-------------+-------------+------------------+

2.将涨幅换算为是否符合涨幅>=5%

执行SQL

SELECT stock_code,
       trade_date,
       if(closing_price / LAG(closing_price) OVER (PARTITION BY stock_code ORDER BY trade_date ASC) - 1 >= 0.05, 1,
          0) AS flag
FROM t4_stock_data
;

执行结果

+-------------+-------------+-------+
| stock_code  | trade_date  | flag  |
+-------------+-------------+-------+
| AAPL        | 2023-02-26  | 0     |
| AAPL        | 2023-02-27  | 1     |
| AAPL        | 2023-02-28  | 1     |
| AAPL        | 2023-03-01  | 1     |
| AAPL        | 2023-03-02  | 1     |
| AAPL        | 2023-03-03  | 1     |
| AAPL        | 2023-03-04  | 1     |
| AAPL        | 2023-03-05  | 1     |
| AAPL        | 2023-03-06  | 0     |
| GOOG        | 2023-02-26  | 0     |
| GOOG        | 2023-02-27  | 1     |
| GOOG        | 2023-02-28  | 1     |
| GOOG        | 2023-03-01  | 0     |
| GOOG        | 2023-03-02  | 1     |
| GOOG        | 2023-03-03  | 0     |
| GOOG        | 2023-03-04  | 1     |
| GOOG        | 2023-03-05  | 1     |
| GOOG        | 2023-03-06  | 1     |
+-------------+-------------+-------+

3.查询结果

执行SQL

with tmp as
         (SELECT stock_code,
                 trade_date,
                 if(closing_price / LAG(closing_price) OVER (PARTITION BY stock_code ORDER BY trade_date ASC) - 1 >=
                    0.05, 1, 0) AS flag
          FROM t4_stock_data),
     tmp2 as
         (select stock_code,
                 trade_date,
                 flag,
                 row_number() over (partition by stock_code order by trade_date asc) -
                 row_number() over (partition by stock_code,flag order by trade_date asc) as diff_rn
          from tmp
          order by stock_code, trade_date)
select stock_code,
       min(trade_date),
       max(trade_date),
       count(1)
From tmp2
Where flag = 1
Group by stock_code, diff_rn
Having count(1) >= 5

查询结果

+-------------+-------------+-------------+------+
| stock_code  |     _c1     |     _c2     | _c3  |
+-------------+-------------+-------------+------+
| AAPL        | 2023-02-27  | 2023-03-05  | 7    |
+-------------+-------------+-------------+------+

四、建表语句和数据插入

--建表语句
CREATE TABLE t4_stock_data (
  stock_code STRING,
  trade_date STRING,
  closing_price DECIMAL(10,2)
);
 
--样例数据插入
INSERT INTO TABLE t4_stock_data
VALUES 
('AAPL', '2023-02-26', 100.00),
('AAPL', '2023-02-27', 105.00),
('AAPL', '2023-02-28', 110.25),
('AAPL', '2023-03-01', 115.78),
('AAPL', '2023-03-02', 121.59),
('AAPL', '2023-03-03', 128.73),
('AAPL', '2023-03-04', 137.00),
('AAPL', '2023-03-05', 144.67),
('AAPL', '2023-03-06', 147.64),
('GOOG', '2023-02-26', 2000.00),
('GOOG', '2023-02-27', 2100.00),
('GOOG', '2023-02-28', 2205.00),
('GOOG', '2023-03-01', 2313.25),
('GOOG', '2023-03-02', 2431.01),
('GOOG', '2023-03-03', 2547.56),
('GOOG', '2023-03-04', 2680.19),
('GOOG', '2023-03-05', 2814.20),
('GOOG', '2023-03-06', 2955.91);

本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；

3.合并连续支付订单5.连续登陆超过N天的用户