-- Simulate user registration and activity data
CREATE TABLE user_activity AS
WITH dates AS (
 SELECT unnest(generate_series(DATE '2025-01-01', DATE '2025-12-31', INTERVAL 1 DAY)) AS day
),
users AS (
 SELECT unnest(generate_series(1, 5000)) AS user_id
),
user_cohorts AS (
 SELECT 
 u.user_id,
 MIN(da.day) AS first_active_day,
 date_trunc('month', MIN(da.day)) AS cohort_month
 FROM users u
 CROSS JOIN dates da
 WHERE random() < 0.3 -- 30% chance of being active on any given day
 GROUP BY u.user_id
),
daily_activity AS (
 SELECT 
 uc.user_id,
 uc.cohort_month,
 date_trunc('month', da.day) AS activity_month,
 COUNT(*) AS active_days
 FROM user_cohorts uc
 CROSS JOIN dates da
 WHERE random() < 0.15 -- 15% monthly active probability
 GROUP BY uc.user_id, date_trunc('month', da.day)
)
SELECT * FROM daily_activity;

-- Core: Calculate retained users per cohort
WITH cohort_counts AS (
 SELECT 
 cohort_month,
 COUNT(DISTINCT user_id) AS cohort_size
 FROM user_activity
 GROUP BY cohort_month
),
monthly_retention AS (
 SELECT 
 cohort_month,
 activity_month,
 datediff('month', cohort_month, activity_month) AS months_since_join,
 COUNT(DISTINCT user_id) AS retained_users
 FROM user_activity
 GROUP BY cohort_month, activity_month, months_since_join
)
SELECT 
 mr.cohort_month,
 mr.months_since_join,
 mr.retained_users,
 cc.cohort_size,
 ROUND(mr.retained_users * 100.0 / cc.cohort_size, 2) AS retention_rate_pct,
 -- Key window function: calculate month-over-month change
 LAG(mr.retained_users) OVER (
 PARTITION BY mr.cohort_month 
 ORDER BY mr.months_since_join
 ) AS prev_month_retained,
 CASE 
 WHEN LAG(mr.retained_users) OVER (
 PARTITION BY mr.cohort_month 
 ORDER BY mr.months_since_join
 ) IS NOT NULL
 THEN ROUND(
 (mr.retained_users - LAG(mr.retained_users) OVER (
 PARTITION BY mr.cohort_month 
 ORDER BY mr.months_since_join
 )) * 100.0 / LAG(mr.retained_users) OVER (
 PARTITION BY mr.cohort_month 
 ORDER BY mr.months_since_join
 ), 2
 )
 ELSE NULL
 END AS mom_change_pct
FROM monthly_retention mr
JOIN cohort_counts cc ON mr.cohort_month = cc.cohort_month
WHERE mr.months_since_join <= 6
ORDER BY mr.cohort_month, mr.months_since_join;

-- Simulate e-commerce order data
CREATE TABLE orders AS
SELECT 
 generate_series(1, 10000) AS order_id,
 date '2025-01-01' + (random() * 365)::INT AS order_date,
 ('electronics'::VARCHAR, 'clothing'::VARCHAR, 'food'::VARCHAR, 'home'::VARCHAR)[floor(random()*4)+1] AS category,
 ROUND((random() * 500 + 10)::NUMERIC, 2) AS amount
FROM generate_series(1, 10000);

-- Daily/weekly GMV + moving average + anomaly detection
WITH daily_gmv AS (
 SELECT 
 order_date::DATE AS day,
 SUM(amount) AS daily_gmv,
 COUNT(*) AS order_count
 FROM orders
 GROUP BY 1
),
enriched AS (
 SELECT 
 day,
 daily_gmv,
 order_count,
 -- 7-day moving average
 AVG(daily_gmv) OVER (
 ORDER BY day 
 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
 ) AS ma_7day,
 -- 30-day moving average
 AVG(daily_gmv) OVER (
 ORDER BY day 
 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
 ) AS ma_30day,
 -- Yesterday comparison (MoM)
 LAG(daily_gmv, 1) OVER (ORDER BY day) AS prev_day_gmv,
 -- Same day last week comparison
 LAG(daily_gmv, 7) OVER (ORDER BY day) AS same_week_prev_gmv,
 -- Standard deviation (for anomaly detection)
 STDDEV(daily_gmv) OVER (
 ORDER BY day 
 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
 ) AS rolling_stddev,
 -- Ranking
 RANK() OVER (
 PARTITION BY EXTRACT(MONTH FROM day) 
 ORDER BY daily_gmv DESC
 ) AS day_rank_in_month,
 -- Cumulative GMV (year-to-date)
 SUM(daily_gmv) OVER (
 ORDER BY day 
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS ytd_gmv
 FROM daily_gmv
)
SELECT 
 day,
 ROUND(daily_gmv, 2) AS daily_gmv,
 ROUND(ma_7day, 2) AS ma_7day,
 ROUND(ma_30day, 2) AS ma_30day,
 ROUND(prev_day_gmv, 2) AS prev_day_gmv,
 ROUND(same_week_prev_gmv, 2) AS same_week_prev_gmv,
 ROUND(ytd_gmv, 2) AS ytd_gmv,
 -- Anomaly flag: exceeds 2 standard deviations
 CASE 
 WHEN ABS(daily_gmv - ma_30day) > 2 * COALESCE(rolling_stddev, daily_gmv * 0.1)
 THEN '⚠️ Anomaly'
 ELSE '✓ Normal'
 END AS anomaly_flag
FROM enriched
ORDER BY day
LIMIT 30;

-- Simulate sales team performance data
CREATE TABLE sales_performance AS
SELECT 
 generate_series(1, 200) AS emp_id,
 ('Zhang San'::VARCHAR, 'Li Si'::VARCHAR, 'Wang Wu'::VARCHAR, 'Zhao Liu'::VARCHAR, 
 'Qian Qi'::VARCHAR, 'Sun Ba'::VARCHAR, 'Zhou Jiu'::VARCHAR, 'Wu Shi'::VARCHAR)
 [floor(random()*8)+1] AS name,
 ('East China'::VARCHAR, 'South China'::VARCHAR, 'North China'::VARCHAR, 'West'::VARCHAR)[floor(random()*4)+1] AS region,
 date '2025-01-01' + (random()*365)::INT AS month_date,
 ROUND((random() * 100000 + 5000)::NUMERIC, 2) AS revenue,
 floor(random() * 50 + 10)::INT AS deals_closed
FROM generate_series(1, 200);

-- Performance ranking + bonus calculation
WITH monthly_stats AS (
 SELECT 
 name,
 region,
 month_date,
 SUM(revenue) AS total_revenue,
 SUM(deals_closed) AS total_deals,
 -- Regional ranking
 RANK() OVER (
 PARTITION BY region, month_date 
 ORDER BY SUM(revenue) DESC
 ) AS region_rank,
 -- Company-wide ranking
 RANK() OVER (
 PARTITION BY month_date 
 ORDER BY SUM(revenue) DESC
 ) AS company_rank,
 -- Gap percentage vs regional average
 ROUND(
 (SUM(revenue) - AVG(SUM(revenue)) OVER (PARTITION BY region, month_date)) 
 * 100.0 / AVG(SUM(revenue)) OVER (PARTITION BY region, month_date), 
 2
 ) AS vs_region_avg_pct,
 -- Month-over-month growth
 LAG(SUM(revenue)) OVER (
 PARTITION BY name, region 
 ORDER BY month_date
 ) AS prev_month_revenue
 FROM sales_performance
 GROUP BY name, region, month_date
)
SELECT 
 name,
 region,
 month_date,
 ROUND(total_revenue, 2) AS total_revenue,
 total_deals,
 region_rank,
 company_rank,
 ROUND(vs_region_avg_pct, 2) AS vs_region_avg_pct,
 ROUND(prev_month_revenue, 2) AS prev_month_revenue,
 CASE 
 WHEN prev_month_revenue IS NOT NULL 
 THEN ROUND((total_revenue - prev_month_revenue) * 100.0 / prev_month_revenue, 2)
 ELSE NULL
 END AS mom_growth_pct,
 -- Bonus calculation: higher rank = higher bonus
 CASE 
 WHEN company_rank = 1 THEN total_revenue * 0.15
 WHEN company_rank <= 3 THEN total_revenue * 0.10
 WHEN company_rank <= 10 THEN total_revenue * 0.05
 ELSE total_revenue * 0.02
 END AS bonus_amount
FROM monthly_stats
ORDER BY month_date, company_rank
LIMIT 50;