Slide 38
Slide 38 text
Kayac. Inc
OLAPなクエリはZero-ETLで
WITH game_action_log as (
SELECT
action_timestamp
,
player_id,
purchase_value
,
purchase_unit
FROM zero_etl.prod.game_action_log
WHERE action_timestamp >= getdate() - interval '1 year'
AND action_type = 'purchase'
), monthly_player as (
SELECT
DATE_TRUNC
(
'MONTH',
DATE(action_timestamp
)
) AS action_month,
player_id,
purchase_unit
,
SUM(purchase_value
) AS total_purchase_value
FROM game_action_log
GROUP BY 1,2,3
)
SELECT
action_month
,
COUNT(distinct player_id) as paid_players,
AVG(total_purchase_value
) as arppu
FROM monthly_player
GROUP BY 1