聚合函数

• 1193. 每月交易 I - 力扣（LeetCode）

select DATE_FORMAT(trans_date, '%Y-%m')       AS month,

       country,

       count(1)                               as trans_count,

       COUNT(IF(state = 'approved', 1, NULL)) AS approved_count,

       sum(amount)                            as trans_total_amount,

       SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount

from Transactions

group by month, country 

• 1174. 即时食物配送 II - 力扣（LeetCode）

select  round (

    sum(order_date = customer_pref_delivery_date) * 100 /

    count(*),

    2

) as immediate_percentage

from Delivery

where  (customer_id, order_date) in (select customer_id, min(order_date) from Delivery group by customer_id);

• 550. 游戏玩法分析 IV - 力扣（LeetCode）

SELECT

    ROUND(

        (select count(1)

        from Activity

        where (player_id, event_date) in

              (

                  select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY)

                  from Activity

                  group by player_id

              )) /

        (SELECT COUNT(DISTINCT player_id)

         FROM Activity),

    2) AS fraction;