Leetcode SQL 开刷

卡在597了，题目本身不难，但是有两个FOLLOW UP, 不知道是不是看看讨论就可以了，还是找个地方做，不规则呀，需要确定对FOLLOWUP 的方针。
ONSITE好像没戏了（从来没有主动投过F家呀，这两年都是他家猎头自己找上来的，今年还搞了个CONFIDENTIAL EMAIL，这种靠SQL和讲故事的面试是不是有点跟不上时代了），回想起来确实是被三哥黑了，根本不按‘确认GOAL’然后‘定义METRIC’然后‘定义假设’然后 ‘分析方案’的套路来，根本就是阴在那边不互动，摄像头挂在墙上他离得还挺远（具体面经待刷新）。投诉了也不抱啥希望了，SQL刷题放缓下，接着刷算法题读PAPER。

'逆水寒秋不染硝尘，挥挥衣袖淡然刷题', 哈哈
2019-09-13
597 将Follow up 的答案在Regular答题处跑一跑，比在NOTEBOOK上干写CODE强一些吧
例如第一个FOLLOW UP
1. 先把TEST CASE改为（六月拷贝一份成七月）：
{"headers":{"friend_request":["sender_id","send_to_id","request_date"],"request_accepted":["requester_id","accepter_id","accept_date"]},"rows":{"friend_request":[[1,2,"2016/06/01"],[1,3,"2016/06/01"],[1,4,"2016/06/01"],[2,3,"2016/06/02"],[3,4,"2016/06/09"], [1,2,"2016/07/01"],[1,3,"2016/07/01"],[1,4,"2016/07/01"],[2,3,"2016/07/02"],[3,4,"2016/07/09"]],"request_accepted":[[1,2,"2016/06/03"],[1,3,"2016/06/08"],[2,3,"2016/06/08"],[3,4,"2016/06/09"],[3,4,"2016/06/10"], [1,2,"2016/07/03"],[1,3,"2016/07/08"],[2,3,"2016/07/08"],[3,4,"2016/07/09"],[3,4,"2016/07/10"]]}}
2. CODE改为：
SELECT r.month, IFNULL(ROUND(a.counts/r.counts, 2), 0) AS accept_rate  FROM (SELECT COUNT(DISTINCT sender_id, send_to_id) AS counts,
               DATE_FORMAT(request_date, '%Y-%m') AS month
        FROM friend_request
        GROUP BY month) r
    INNER JOIN
       (SELECT COUNT(DISTINCT requester_id, accepter_id) AS counts,
               DATE_FORMAT(accept_date, '%Y-%m') AS month
        FROM request_accepted
        GROUP BY month) a
    ON r.month = a.month

3. 运行结果：
{"headers":["month","accept_rate"],"values":[["2016-06",0.8],["2016-07",0.8]]}

2019-09-14
619
596
197
1142
196? (GROUP BY does not work)
176
1077
1126
终于吧EASY都搞定了， 走比站强，争取每天做两道MEDIUM或者一道HARD)

jan2019 发表于 2019-9-12 00:04
多谢。才知道leetcode sql。之前知道sqlzoo没有去看。

SQLZOO挺好的，平时我也会去做做，但是那都是一组十道题（还有专题的比如Adventure），需要连续的做，楼主的速度还没有跟上,等把LEETCODE都刷完了可以周末专攻SQLZOO。

1045
534

570
1112 这个题也是不太考虑NULL的情况了
608
Union　＋ IF　解法：
# Write your MySQL query statement below
SELECT id, IF(p_id IS NOT NULL, 'child', 'Root') AS Type
           FROM tree
           WHERE p_id IS NULL
UNION
SELECT DISTINCT p.id AS id, IF(c.p_id IS NOT NULL, 'Inner', 'Leaf') AS Type
  FROM tree p
  LEFT JOIN tree c

  ON p.id=c.p_id
  WHERE p.p_id IS NOT NULL
ORDER BY id

1193 100% Solution
# Write your MySQL query statement below
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
       country,
       COUNT(*) AS trans_count,
       IFNULL(SUM(CASE state WHEN 'approved' THEN 1 ELSE 0 END), 0)
                AS approved_count,
       SUM(IFNULL(amount, 0)) AS trans_total_amount,
       IFNULL(SUM(CASE state WHEN 'approved' THEN amount ELSE 0 END), 0)
                AS approved_total_amount
            
    FROM Transactions
    GROUP BY month, country