Casino & Gaming Analytics

-- models/silver/slv_player_360.sql
-- Unified player profile combining gaming, F&B, hotel, and loyalty data

{{ config(
    materialized='incremental',
    unique_key='player_id',
    schema='silver'
) }}

with gaming_summary as (
    select
        player_id,
        count(distinct session_id)          as total_sessions,
        sum(coin_in)                        as lifetime_coin_in,
        sum(coin_out)                       as lifetime_coin_out,
        sum(theo_win)                       as lifetime_theo_win,
        sum(actual_win)                     as lifetime_actual_win,
        avg(session_duration_min)           as avg_session_duration_min,
        max(session_end_ts)                 as last_gaming_activity,
        min(session_start_ts)              as first_gaming_activity,
        count(distinct machine_id)          as unique_machines_played,
        mode() within group (order by floor_section)  as preferred_floor_section,
        mode() within group (order by denomination)   as preferred_denomination
    from {{ ref('brz_player_sessions') }}
    group by player_id
),

fb_summary as (
    select
        player_id,
        count(distinct check_id)    as total_fb_visits,
        sum(total)                  as lifetime_fb_spend,
        sum(comp_amount)            as lifetime_fb_comps,
        mode() within group (order by outlet)  as favorite_outlet,
        max(transaction_ts)         as last_fb_visit
    from {{ ref('brz_fb_transactions') }}
    where player_id is not null
    group by player_id
),

hotel_summary as (
    select
        player_id,
        count(distinct reservation_id)  as total_stays,
        sum(nights)                     as lifetime_room_nights,
        sum(case when comp_flag then 1 else 0 end)  as comp_room_nights,
        avg(rate)                       as avg_daily_rate,
        max(checkout_date)              as last_hotel_stay
    from {{ ref('brz_hotel_reservations') }}
    where player_id is not null
    group by player_id
),

loyalty as (
    select
        player_id,
        tier,
        points_balance,
        signup_date,
        lifetime_theo    as loyalty_lifetime_theo,
        lifetime_actual  as loyalty_lifetime_actual
    from {{ ref('brz_loyalty_members') }}
)

select
    coalesce(g.player_id, f.player_id, h.player_id, l.player_id)  as player_id,
    l.tier                          as loyalty_tier,
    l.points_balance,
    l.signup_date,

    -- Gaming metrics
    g.total_sessions,
    g.lifetime_coin_in,
    g.lifetime_coin_out,
    g.lifetime_theo_win,
    g.lifetime_actual_win,
    g.avg_session_duration_min,
    g.preferred_floor_section,
    g.preferred_denomination,
    g.unique_machines_played,
    g.first_gaming_activity,
    g.last_gaming_activity,

    -- F&B metrics
    f.total_fb_visits,
    f.lifetime_fb_spend,
    f.lifetime_fb_comps,
    f.favorite_outlet,
    f.last_fb_visit,

    -- Hotel metrics
    h.total_stays,
    h.lifetime_room_nights,
    h.comp_room_nights,
    h.avg_daily_rate,
    h.last_hotel_stay,

    -- Derived: days since last visit (any touchpoint)
    datediff(
        day,
        greatest(
            coalesce(g.last_gaming_activity, '1900-01-01'),
            coalesce(f.last_fb_visit, '1900-01-01'),
            coalesce(h.last_hotel_stay, '1900-01-01')
        ),
        current_date
    ) as days_since_last_visit,

    -- Derived: engagement score (0-100)
    least(100, (
        coalesce(g.total_sessions, 0) * 0.3
        + coalesce(f.total_fb_visits, 0) * 2.0
        + coalesce(h.total_stays, 0) * 5.0
        + case when l.tier = 'Platinum' then 20
               when l.tier = 'Gold' then 15
               when l.tier = 'Silver' then 10
               else 5 end
    )) as engagement_score,

    current_timestamp as _loaded_at

from gaming_summary g
full outer join fb_summary f on g.player_id = f.player_id
full outer join hotel_summary h on g.player_id = h.player_id
full outer join loyalty l on g.player_id = l.player_id

-- Gold layer: Comp ROI by loyalty tier
-- models/gold/gld_comp_roi_analysis.sql

{{ config(materialized='table', schema='gold') }}

select
    p.loyalty_tier,
    count(distinct p.player_id)                         as player_count,
    round(avg(p.lifetime_theo_win), 2)                  as avg_theo_win,
    round(sum(p.lifetime_fb_comps +
        p.comp_room_nights * p.avg_daily_rate), 2)      as total_comp_cost,
    round(sum(p.lifetime_coin_in - p.lifetime_coin_out), 2)  as total_gaming_revenue,
    round(
        sum(p.lifetime_coin_in - p.lifetime_coin_out) /
        nullif(sum(p.lifetime_fb_comps +
            p.comp_room_nights * p.avg_daily_rate), 0),
        2
    )                                                    as comp_roi_ratio,
    round(avg(p.engagement_score), 1)                   as avg_engagement_score,
    round(avg(p.days_since_last_visit), 0)              as avg_days_since_visit
from {{ ref('slv_player_360') }} p
group by p.loyalty_tier
order by comp_roi_ratio desc

-- models/gold/gld_aml_ctr_candidates.sql
-- Identify CTR-reportable transactions: aggregate cash activity > $10,000 per gaming day

{{ config(materialized='incremental', schema='gold', unique_key='player_gaming_day') }}

with daily_cash_activity as (
    select
        s.player_id,
        cast(s.session_start_ts as date)      as gaming_day,
        sum(s.cash_buy_in)                    as total_buy_in,
        sum(s.cash_out)                       as total_cash_out,
        sum(s.cash_buy_in) + sum(s.cash_out)  as total_cash_activity,
        count(distinct s.session_id)          as session_count,
        array_agg(distinct s.machine_id)      as machines_used
    from {{ ref('brz_player_sessions') }} s
    group by s.player_id, cast(s.session_start_ts as date)
),

structuring_detection as (
    -- Flag transactions just below $10,000 threshold (potential structuring)
    select
        player_id,
        gaming_day,
        total_cash_activity,
        session_count,
        case
            when total_cash_activity between 8000 and 9999
                 and session_count >= 3
            then true
            else false
        end as structuring_flag
    from daily_cash_activity
)

select
    d.player_id || '-' || d.gaming_day  as player_gaming_day,
    d.player_id,
    d.gaming_day,
    d.total_buy_in,
    d.total_cash_out,
    d.total_cash_activity,
    d.session_count,
    d.machines_used,
    case
        when d.total_cash_activity >= 10000 then 'CTR_REQUIRED'
        when s.structuring_flag then 'SAR_REVIEW'
        else 'BELOW_THRESHOLD'
    end as compliance_action,
    current_timestamp as _evaluated_at
from daily_cash_activity d
left join structuring_detection s
    on d.player_id = s.player_id and d.gaming_day = s.gaming_day
where d.total_cash_activity >= 8000
   or s.structuring_flag

{% if is_incremental() %}
    and d.gaming_day > (select max(gaming_day) from {{ this }})
{% endif %}

-- models/gold/gld_nigc_revenue_report.sql
-- Monthly revenue summary formatted for NIGC regulatory submission

{{ config(materialized='table', schema='gold') }}

select
    date_trunc('month', gaming_day)          as report_month,
    game_category,
    count(distinct machine_id)               as machine_count,
    sum(coin_in)                             as total_coin_in,
    sum(coin_out)                            as total_coin_out,
    sum(coin_in) - sum(coin_out)             as gross_gaming_revenue,
    round((sum(coin_in) - sum(coin_out)) / nullif(sum(coin_in), 0) * 100, 2)
                                              as hold_percentage,
    sum(jackpot_amount)                      as total_jackpots_paid,
    count(distinct player_id)                as unique_players
from {{ ref('slv_gaming_events_daily') }}
group by date_trunc('month', gaming_day), game_category
order by report_month desc, game_category