OpenLake:Open source lakehouse

DROP TABLE IF EXISTS game_db.dwd_user_details;

CREATE TABLE game_db.dwd_user_details
USING paimon
AS
WITH p AS (
  SELECT
    user_id,
    decode(gender, 'UTF-8') AS gender,
    decode(os_version, 'UTF-8') AS os_version,
    CAST(decode(current_level, 'UTF-8') AS INT) AS current_level,
    decode(device_type, 'UTF-8') AS device_type,
    TO_DATE(decode(last_login_date, 'UTF-8')) AS last_login_date,
    decode(favorite_game_mode, 'UTF-8') AS favorite_game_mode,
    decode(language_preference, 'UTF-8') AS language_preference,
    decode(active_time, 'UTF-8') AS active_time,
    TO_DATE(decode(registration_date, 'UTF-8')) AS registration_date,
    CAST(decode(total_deaths, 'UTF-8') AS INT) AS total_deaths,
    CAST(decode(game_hours, 'UTF-8') AS DOUBLE) AS game_hours,
    decode(location, 'UTF-8') AS location,
    decode(play_frequency, 'UTF-8') AS play_frequency,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY TO_DATE(decode(last_login_date, 'UTF-8')) DESC NULLS LAST
    ) AS rn
  FROM game_db.ods_user_profile
)
SELECT * EXCEPT (rn)
FROM p
WHERE rn = 1;  -- Deduplicate: Keep only the record with the latest last_login_date for each user_id

DROP TABLE IF EXISTS game_db.dwd_user_event;

CREATE TABLE game_db.dwd_user_event
USING paimon
AS
WITH e AS (
  SELECT
    user_id,
    LOWER(decode(event_type, 'UTF-8')) AS event_type,
    TRIM(decode(timestamp, 'UTF-8')) AS ts_str,
    decode(event_details, 'UTF-8') AS event_details,
    decode(location, 'UTF-8') AS event_location
  FROM game_db.ods_user_event
),
e_ts AS (
  SELECT *,
    CASE
      WHEN ts_str RLIKE '^[0-9]{13}$' THEN TO_TIMESTAMP(FROM_UNIXTIME(CAST(ts_str AS BIGINT)/1000))
      WHEN ts_str RLIKE '^[0-9]{10}$' THEN TO_TIMESTAMP(FROM_UNIXTIME(CAST(ts_str AS BIGINT)))
      ELSE TO_TIMESTAMP(ts_str)
    END AS event_ts
  FROM e
)
SELECT
  e.user_id,
  e.event_ts,
  TO_DATE(e.event_ts) AS event_date,
  e.event_type,
  COALESCE(NULLIF(e.event_location,''), d.location) AS location,
  -- Extract amount from JSON
  COALESCE(CAST(get_json_object(event_details, '$.amount') AS DOUBLE), 0.0) AS amount,
  d.gender, d.device_type
FROM e_ts e
LEFT JOIN game_db.dwd_user_details d ON e.user_id = d.user_id
WHERE e.event_ts IS NOT NULL;

DROP TABLE IF EXISTS game_db.ads_retention_daily;

CREATE TABLE game_db.ads_retention_daily
USING paimon
AS
WITH dau AS (
  SELECT event_date AS dt, user_id
  FROM game_db.dwd_user_event
  GROUP BY event_date, user_id
)
SELECT
  base.dt,
  base.dau,
  COALESCE(d1.d1_retained, 0) AS d1_retained,
  ROUND(COALESCE(d1.d1_retained,0) * 1.0 / base.dau, 4) AS d1_retention_rate,
  COALESCE(d7.d7_retained, 0) AS d7_retained,
  ROUND(COALESCE(d7.d7_retained,0) * 1.0 / base.dau, 4) AS d7_retention_rate
FROM (
  SELECT dt, COUNT(DISTINCT user_id) AS dau FROM dau GROUP BY dt
) base
LEFT JOIN (
  SELECT a.dt, COUNT(DISTINCT a.user_id) AS d1_retained
  FROM dau a JOIN dau b ON a.user_id = b.user_id AND b.dt = DATE_ADD(a.dt, 1)
  GROUP BY a.dt
) d1 ON base.dt = d1.dt
LEFT JOIN (
  SELECT a.dt, COUNT(DISTINCT a.user_id) AS d7_retained
  FROM dau a JOIN dau b ON a.user_id = b.user_id AND b.dt = DATE_ADD(a.dt, 7)
  GROUP BY a.dt
) d7 ON base.dt = d7.dt;