题目地址

https://leetcode.cn/problems/friday-purchases-i/description/

代码

# Write your MySQL query statement below

with t1 as(
    SELECT
    *,
    DAYOFMONTH(purchase_date) as day_of_month,
    DAYOFWEEK(purchase_date) as day_of_week,
    FLOOR((DAYOFMONTH(purchase_date) + DAYOFWEEK(CONCAT(YEAR(purchase_date), '-', MONTH(purchase_date), '-01')) - 2) / 7) + 1 as week_of_month
    FROM
    Purchases
)
,
t2 as(
    select week_of_month,  purchase_date,
    sum(amount_spend) as total_amount
    from t1
    where day_of_week=6 #周五
    and date_format(purchase_date, "%Y-%m") ="2023-11"
    group by week_of_month,  purchase_date
    order by week_of_month asc
)

select * from t2