Danny's Diner Question 10

[A-C-Sai]

The solution given appears to have a problem. According to the solution Customer A got 1020 points and Customer B got 320.

I wasn't able to fully figure out where the problem is but I think it has to do with the join conditions. I have done the calculations by hand and through query and it resulted in Customer A with 1370 points and Customer B with 820 points.

Please correct me if I am wrong.

Below is the query that I used (MySQL):

    with date_cte as (
        select
            *,
            DATE_ADD(m.join_date, INTERVAL 6 DAY) as bonus_end
        from 
        members m
    )

    select 
        date_cte.customer_id,
        SUM(
            case
                when order_date between join_date and bonus_end then price*10*2
                when product_name = "sushi" then price*10*2
                else price*10
            end
        ) as points
    from 
        date_cte
        left join
        sales s
        on date_cte.customer_id = s.customer_id
        join
        menu m
        on m.product_id = s.product_id
    where MONTH(order_date) <= 1
    group by 1
    order by 1