sql-1

-- 连续登录平均率 ★★★
-- 输入：
-- Activity table:
-- +-----------+-----------+------------+--------------+
-- | player_id | device_id | event_date | games_played |
-- +-----------+-----------+------------+--------------+
-- | 1 | 2 | 2016-03-01 | 5 |
-- | 1 | 2 | 2016-03-02 | 6 |
-- | 2 | 3 | 2017-06-25 | 1 |
-- | 3 | 1 | 2016-03-02 | 0 |
-- | 3 | 4 | 2018-07-03 | 5 |
-- +-----------+-----------+------------+--------------+
-- 输出：
-- +-----------+
-- | fraction |
-- +-----------+
-- | 0.33 |
-- +-----------+
-- 解释：
-- 只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33
SELECT * FROM tmp SELECT
round( count( DISTINCT t2.player_id )/ count( DISTINCT t1.player_id ), 2 ) fraction
FROM
( SELECT player_id, min( event_date ) first_date FROM activity GROUP BY player_id ) t1
LEFT JOIN activity t2 ON t1.player_id = t2.player_id
AND datediff( t2.event_date, t1.first_date )= 1


-- ---------------------------------
-- +-------------+-------+
-- | Column Name | Type |
-- +-------------+-------+
-- | pid | int |
-- | tiv_2015 | float |
-- | tiv_2016 | float |
-- | lat | float |
-- | lon | float |
-- +-------------+-------+
-- pid 是这张表的主键(具有唯一值的列)。
-- 表中的每一行都包含一条保险信息，其中：
-- pid 是投保人的投保编号。
-- tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。
-- lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。
-- lon 是投保人所在城市的经度。题目数据确保 lon 不为空。
--
--
-- 编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：
--
-- 他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
-- 他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。
-- tiv_2016 四舍五入的 两位小数 。
--
-- 查询结果格式如下例所示。

-- myql 8.0 写法 ☆☆☆☆☆☆☆☆☆☆☆☆☆☆

SELECT ROUND(SUM(tiv_2016),2) tiv_2016
FROM
(
SELECT *,
COUNT(*) OVER(PARTITION BY tiv_2015 ) cnt_tiv_2015,
COUNT(*) OVER(PARTITION BY lat,lon ) cnt_lat
FROM Insurance
)t
WHERE cnt_tiv_2015>1 AND cnt_lat=1

-- myql 8.0 以下版本 写法 ☆☆☆☆☆☆☆☆☆☆☆☆☆☆

select sum(t2.tiv_2016) tiv_2016 from (
select * ,
(select count(1) from Insurance where t1.tiv_2015 =tiv_2015 ) as ty,
(select count(1) from Insurance where t1.lat =lat and t1.lon =lon ) as tc

FROM Insurance t1
) t2
where t2.ty > 1 and t2.tc = 1






---------------------------- 双向好友查询 ----------------------------------------

SELECT id, num
FROM (
SELECT id, COUNT(*) AS num
FROM (
SELECT requester_id AS id FROM RequestAccepted
UNION ALL
SELECT accepter_id AS id FROM RequestAccepted
) AS all_friends
GROUP BY id
) AS friend_count
WHERE num = (
SELECT MAX(num)
FROM (
SELECT COUNT(*) AS num
FROM (
SELECT requester_id AS id FROM RequestAccepted
UNION ALL
SELECT accepter_id AS id FROM RequestAccepted
) AS all_friends
GROUP BY id
) AS counts
);





-- mysql 8 支持的 LAG 写法

with people as
(
select id, visit_date, people,
Lag(people,2) over(order by id) as pprvPeople,
Lag(people,1) over(order by id) as prvPeople,
Lead(people,1) over(order by id) as nextPeople,
Lead(people,2) over(order by id) as nnextPeople
from stadium
)
select id, visit_date, people from people
where
(people >= 100 and prvPeople>=100 and pprvPeople>=100) ||
(people >= 100 and nextPeople>=100 and nnextPeople>=100) ||
(people >= 100 and nextPeople>=100 and prvPeople>=100)