-- Primary data source: Real e-commerce transactions
-- Location: `bigquery-public-data.thelook_ecommerce.*`

-- Core tables:
- users (100K+ customers)           -- Customer profiles & demographics
- orders (200K+ transactions)       -- Order history & status
- order_items (400K+ line items)    -- Product-level transaction details
- products (30K+ SKUs)              -- Product catalog & pricing

-- Check customer dimension
SELECT 
    customer_segment,
    COUNT(*) as customers,
    AVG(lifetime_value) as avg_ltv
FROM `flit-data-platform.flit_marts.dim_customers`
GROUP BY customer_segment;

-- Check experiment results
SELECT 
    experiment_name,
    variant,
    exposed_users,
    conversion_rate_30d
FROM `flit-data-platform.flit_marts.experiment_results`;

-- dim_customers: Complete customer profile with behavioral metrics
SELECT 
    user_id,
    full_name,
    age_segment,
    country,
    acquisition_channel,
    registration_date,
    
    -- Order metrics
    lifetime_orders,
    lifetime_value,
    avg_order_value,
    days_since_last_order,
    
    -- Behavioral indicators
    categories_purchased,
    brands_purchased,
    avg_items_per_order,
    
    -- Segmentation
    customer_segment,      -- 'VIP', 'Regular', 'One-Time', etc.
    lifecycle_stage       -- 'Active', 'At Risk', 'Dormant', 'Lost'
    
FROM {{ ref('dim_customers') }}

-- experiment_results: A/B test performance by variant
SELECT 
    experiment_name,
    variant,
    exposed_users,
    conversions_30d,
    conversion_rate_30d,
    avg_revenue_per_user,
    statistical_significance
    
FROM {{ ref('experiment_results') }}
WHERE experiment_name = 'checkout_button_color'
ORDER BY conversion_rate_30d DESC

-- ltv_features: Customer lifetime value prediction features
SELECT 
    user_id,
    
    -- RFM features
    days_since_last_order as recency,
    lifetime_orders as frequency, 
    avg_order_value as monetary,
    
    -- Behavioral features
    categories_purchased,
    avg_days_between_orders,
    seasonal_purchase_pattern,
    
    -- Engagement features
    experiments_participated,
    support_ticket_ratio,
    
    -- Target (90-day future revenue)
    target_90d_revenue
    
FROM {{ ref('ltv_features') }}

-- ❌ Expensive query (3.2GB processed, $0.016)
SELECT 
    user_id,
    COUNT(*) as total_orders,
    SUM(sale_price) as total_revenue
FROM `bigquery-public-data.thelook_ecommerce.order_items`
GROUP BY user_id
ORDER BY total_revenue DESC

-- ✅ Optimized query (0.8GB processed, $0.004)
SELECT 
    user_id,
    COUNT(*) as total_orders,
    SUM(sale_price) as total_revenue
FROM `bigquery-public-data.thelook_ecommerce.order_items`
WHERE created_at >= '2023-01-01'  -- Partition filter
    AND status = 'Complete'        -- Early filter
GROUP BY user_id
ORDER BY total_revenue DESC
LIMIT 1000                         -- Limit results

-- tests/test_experiment_balance.sql
-- Ensure A/B test variants are properly balanced (within 5%)
WITH variant_distribution AS (
    SELECT 
        experiment_name,
        variant,
        COUNT(*) as user_count,
        COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY experiment_name) as allocation_pct
    FROM {{ ref('stg_experiment_assignments') }}
    GROUP BY experiment_name, variant
)
SELECT *
FROM variant_distribution
WHERE ABS(allocation_pct - 0.333) > 0.05  -- Flag imbalanced experiments