배송 예정일 예측 성공과 실패 & 레스토랑의 대목 (solvesql)

배송 예정일 예측 성공과 실패

https://solvesql.com/problems/estimated-delivery-date/

with
  AA AS (
    SELECT
      date_format(order_purchase_timestamp, '%Y-%m-%d') AS purchase_date,
      CASE
        WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN 1
        ELSE 0
      END Success,
      CASE
        WHEN order_delivered_customer_date > order_estimated_delivery_date THEN 1
        ELSE 0
      END fail
    FROM
      olist_orders_dataset
    WHERE
      DATE_FORMAT(order_purchase_timestamp, '%y-%m') = '17-01'
      AND order_delivered_customer_date is not null
      AND order_estimated_delivery_date is not null
  )
SELECT
  purchase_date,
  sum(Success) success,
  sum(fail) fail
FROM
  AA
GROUP BY
  purchase_date
ORDER BY
  purchase_date

레스토랑의 대목

https://solvesql.com/problems/high-season-of-restaurant/

SELECT
  *
FROM
  tips
WHERE
  day in (
    SELECT
      day
    FROM
      tips
    GROUP BY
      day
    HAVING
      sum(total_bill) >= 1500
  )