일별 MTD ADO 계산: SQL 쿼리로 일별 MTD 평균 주문량 분석하기

데이터 분석가들은 종종 특정 시점에서 MTD(월간 누적) 평균 주문 건수, M-1 평균 주문 건수, M-3 평균 주문 건수 등을 계산해야 합니다. 이번 포스팅에서는 특정 시점이 아닌 일별로 MTD 평균 주문 건수를 계산하는 방법을 공유하려 합니다. 

일별 MAU 계산도 동일한 로직으로 SQL 사용하여 계산 가능 하니 참조해 주세요.

우선 MTD ADO에 대해 설명하겠습니다. MTD ADO는 Month-To-Date Average Daily Orders의 약자로, 현재 달의 시작일부터 오늘까지의 누적 주문 수를 해당 기간의 일수로 나눈 값을 의미합니다. 이는 특정 달의 일별 주문량 평균을 파악하는 데 유용합니다. 예를 들어, 특정 달의 15일까지의 주문량을 15일로 나누면, 그 달의 일별 평균 주문량을 구할 수 있습니다.

이제 실제 쿼리를 통해 일별 MTD ADO를 계산하는 방법을 살펴보겠습니다. 아래와 같은 쿼리 아이디어를 기반으로 작성하시면 됩니다. (Presto SQL 기준입니다.)

with order_table as (
    select
        order_date,
        order_id
    from
        order_table
),
date_info as (
    select
        order_date as mtd_order_date_to,
        date_trunc('month', order_date) as mtd_order_date_from
    from
        order_table
    group by
        1,2
)
select  
    order_date,
    mtd_gross_orders / days_count as mtd_ado
from (
    select
        d.mtd_order_date_to as order_date,
        count(distinct o.order_id) as mtd_gross_orders,
        cast(count(distinct o.order_date) as double) as days_count
    from
        order_table as o
    join
        date_info as d
    on
        o.order_date between d.mtd_order_date_from and d.mtd_order_date_to
    group by
        d.mtd_order_date_to
)
order by
    1

order_table과 date_info 테이블을 조인할 때, 중요한 점은 order_table의 order_date를 date_info 테이블에서 생성한 mtd_order_date_from과 mtd_order_date_to 사이에 포함시키는 것입니다. 이를 위해 BETWEEN 조건을 사용하여 두 테이블을 조인하게 됩니다. 이 조인 과정을 통해, order_table의 각 주문이 속한 월간 기간 내에서의 데이터를 가져올 수 있으며, 이후 이 데이터를 활용해 MTD 평균 주문 건수를 계산할 수 있습니다.

최종적으로 mtd_order_date_to를 주문 건 확인 날짜로 선택하여 SELECT문에 포함시키면, 특정 날짜까지의 MTD 평균 주문건수를 일자별로 확인할 수 있습니다.