[SQL] solvesql | 쇼핑몰의 일일 매출액과 ARPPU

문제

https://solvesql.com/problems/daily-arppu/

https://solvesql.com/problems/daily-arppu/

 

코드

select 
  date(o.order_purchase_timestamp) as dt,
  count(distinct o.order_id) as pu,
  sum(p.payment_value) as revenue_daily,
  round(sum(p.payment_value) / count(distinct o.order_id), 2) as arppu 
from olist_orders_dataset as o
join olist_order_payments_dataset as p
  on o.order_id = p.order_id
where date(o.order_purchase_timestamp) >= "2018-01-01"
group by 1
order by 1

-- 15분 성공

 

문제 풀이

SQLite 시간 함수

 

• date (컬럼명) : 2024-02-14 반환
• strftime ('%Y-%m-%d', 컬럼명) : 2024-02-14 반환
• time (컬럼명) : 시간 추출
• julianday (컬럼명) : 날짜를 숫자 형식으로 반환, 1이 1일

 

느낀 점

없다