-- =============================================================
-- File: Portfolio_Analytics_Demo_MohammedBeheiry.sql
-- Author: Mohammed Beheiry
-- Role: Product & Business Analyst
-- Purpose:
--   Demonstrates full SQL analytics workflow:
--   1. Create messy operational data
--   2. Clean and standardize it
--   3. Build KPI summary tables
--   4. Run common product/business analytics queries
--   5. Clean up environment
-- =============================================================


-- =============================================================
-- SECTION 1️⃣ : CREATE RAW DATA (Messy Sample)
-- =============================================================

BEGIN;

DROP TABLE IF EXISTS raw_daily_sales;

CREATE TABLE raw_daily_sales (
    id SERIAL PRIMARY KEY,
    sale_date TEXT,
    customer_id TEXT,
    product_id TEXT,
    quantity TEXT,
    unit_price TEXT,
    region TEXT,
    discount TEXT,
    salesperson TEXT,
    loaded_at TIMESTAMP DEFAULT NOW()
);

-- Insert intentionally messy data (nulls, duplicates, inconsistent formats)
INSERT INTO raw_daily_sales (sale_date, customer_id, product_id, quantity, unit_price, region, discount, salesperson)
VALUES
('2025-10-01', 'C001', 'P001', '3', '25.50', 'North', '0.1', 'Alice'),
('2025/10/02', 'C002', 'P002', '5', '19', 'south ', ' ', 'Bob'),
('10-03-2025', 'C003', 'P003', 'two', '30.00', 'East', NULL, 'Charlie'),
('2025-10-03', 'C003', 'P003', '2', '30.00', 'East', '0.05', 'Charlie'), -- duplicate
('2025-10-04', 'C004', NULL, '4', '', 'West', '0.2', 'Dana'),
('2025-10-05', 'C005', 'P005', NULL, '15.00', 'North', '0.05', 'Alice'),
('2025-10-06', 'C006', 'P006', '6', 'not available', 'SOUTH', '0.1', 'Bob'),
('2025-10-07', 'C007', 'P007', '4', '25.00', NULL, NULL, 'Charlie');

COMMIT;

-- SELECT * FROM raw_daily_sales;


-- =============================================================
-- SECTION 2️⃣ : DATA CLEANING PIPELINE
-- =============================================================

BEGIN;

DROP TABLE IF EXISTS clean_daily_sales;

CREATE TABLE clean_daily_sales AS
WITH step1_standardized AS (
    SELECT
        -- ✅ Standardize date formats
        CASE
            WHEN sale_date ~ '^\d{4}-\d{2}-\d{2}$' THEN TO_DATE(sale_date, 'YYYY-MM-DD')
            WHEN sale_date ~ '^\d{4}/\d{2}/\d{2}$' THEN TO_DATE(sale_date, 'YYYY/MM/DD')
            WHEN sale_date ~ '^\d{2}-\d{2}-\d{4}$' THEN TO_DATE(sale_date, 'MM-DD-YYYY')
            ELSE NULL
        END AS sale_date,

        -- ✅ Trim and clean text fields
        BTRIM(customer_id) AS customer_id,
        INITCAP(BTRIM(region)) AS region,

        -- ✅ Fix invalid quantity values
        CASE
            WHEN quantity ~ '^\d+$' THEN quantity::INTEGER
            WHEN LOWER(quantity) = 'two' THEN 2
            ELSE NULL
        END AS quantity,

        -- ✅ Handle empty strings and invalid unit_price
        CASE
            WHEN unit_price ~ '^\d+(\.\d+)?$' THEN unit_price::NUMERIC(10,2)
            ELSE NULL
        END AS unit_price,

        -- ✅ Replace null discounts with default 0
        COALESCE(NULLIF(BTRIM(discount), '')::NUMERIC, 0.0) AS discount,

        -- ✅ Normalize salesperson names
        INITCAP(BTRIM(salesperson)) AS salesperson,

        product_id,
        loaded_at
    FROM raw_daily_sales
),
step2_filtered AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id, product_id, sale_date ORDER BY loaded_at DESC
        ) AS rn
    FROM step1_standardized
    WHERE sale_date IS NOT NULL
      AND customer_id IS NOT NULL
      AND product_id IS NOT NULL
)
SELECT
    sale_date,
    customer_id,
    product_id,
    quantity,
    unit_price,
    region,
    discount,
    salesperson,
    -- ✅ Derived metric
    ROUND(COALESCE(quantity, 0) * COALESCE(unit_price, 0) * (1 - discount), 2) AS net_revenue,
    loaded_at
FROM step2_filtered
WHERE rn = 1;

COMMIT;

-- SELECT * FROM clean_daily_sales;


-- =============================================================
-- SECTION 3️⃣ : KPI SUMMARY TABLES
-- =============================================================

BEGIN;

-- ✅ Daily KPIs
DROP TABLE IF EXISTS kpi_daily_summary;
CREATE TABLE kpi_daily_summary AS
SELECT
    sale_date,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(quantity) AS total_items_sold,
    ROUND(SUM(net_revenue), 2) AS total_revenue,
    ROUND(AVG(net_revenue), 2) AS avg_revenue_per_transaction
FROM clean_daily_sales
GROUP BY sale_date
ORDER BY sale_date;

-- ✅ Regional KPIs
DROP TABLE IF EXISTS kpi_region_summary;
CREATE TABLE kpi_region_summary AS
SELECT
    region,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(net_revenue) AS total_revenue,
    ROUND(AVG(net_revenue), 2) AS avg_transaction_value
FROM clean_daily_sales
GROUP BY region
ORDER BY total_revenue DESC;

COMMIT;

-- SELECT * FROM kpi_daily_summary;
-- SELECT * FROM kpi_region_summary;


-- =============================================================
-- SECTION 4️⃣ : BUSINESS ANALYTICS (KPI PRACTICE QUERIES)
-- =============================================================

BEGIN;

-- Create mock datasets for KPI queries -------------------------
DROP TABLE IF EXISTS signups;
CREATE TABLE signups (user_id INT, signup_date DATE);
INSERT INTO signups VALUES
(1, '2025-01-01'), (2, '2025-01-02'), (3, '2025-01-03'),
(4, '2025-01-04'), (5, '2025-01-05');

DROP TABLE IF EXISTS subscriptions;
CREATE TABLE subscriptions (user_id INT, signup_date DATE, churned_date DATE);
INSERT INTO subscriptions VALUES
(1, '2024-12-01', '2025-01-10'),
(2, '2024-12-15', NULL),
(3, '2024-12-20', '2025-01-25'),
(4, '2024-12-25', NULL);

DROP TABLE IF EXISTS events;
CREATE TABLE events (event_id SERIAL, user_id INT, event TEXT, event_date DATE);
INSERT INTO events (user_id, event, event_date) VALUES
(1, 'signup', '2025-01-01'),
(1, 'paid', '2025-01-10'),
(2, 'signup', '2025-01-02'),
(3, 'signup', '2025-01-03'),
(3, 'paid', '2025-01-25');

DROP TABLE IF EXISTS purchases;
CREATE TABLE purchases (user_id INT, purchase_date DATE, amount NUMERIC);
INSERT INTO purchases VALUES
(1, '2025-01-05', 50.00),
(1, '2025-01-10', 75.00),
(2, '2025-01-12', 60.00),
(3, '2025-01-25', 90.00);

DROP TABLE IF EXISTS survey_responses;
CREATE TABLE survey_responses (user_id INT, score INT);
INSERT INTO survey_responses VALUES
(1,9),(2,10),(3,7),(4,6),(5,4);


-- 1️⃣ Basics: Filters & Counts -------------------------------
SELECT COUNT(DISTINCT user_id)
FROM signups
WHERE signup_date BETWEEN '2025-01-01' AND '2025-01-31';


-- 2️⃣ Aggregations: New Users / Churn --------------------------
SELECT signup_date, COUNT(DISTINCT user_id) AS new_users
FROM signups
GROUP BY signup_date;

SELECT 
  COUNT(DISTINCT user_id) FILTER (WHERE churned_date IS NOT NULL) * 1.0 /
  COUNT(DISTINCT user_id) AS churn_rate
FROM subscriptions
WHERE signup_date < '2025-01-01'
  AND churned_date BETWEEN '2025-01-01' AND '2025-01-31';


-- 3️⃣ Joins: Combining User + Event Data -----------------------
SELECT
    s.user_id,
    s.signup_date,
    COUNT(e.event_id) AS events_in_period
FROM signups s
LEFT JOIN events e
    ON s.user_id = e.user_id
   AND e.event_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY s.user_id, s.signup_date;


-- 4️⃣ Window Functions: First Purchase -------------------------
SELECT user_id, purchase_date,
       ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY purchase_date) AS purchase_rank
FROM purchases;


-- 5️⃣ Funnel Conversion: Signup → Paid -------------------------
SELECT
  COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END) AS signups,
  COUNT(DISTINCT CASE WHEN event = 'paid' THEN user_id END) AS paid_users,
  ROUND(
    COUNT(DISTINCT CASE WHEN event = 'paid' THEN user_id END) * 1.0 /
    COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END), 2
  ) AS conversion_rate
FROM events
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';


-- 6️⃣ NPS & CSAT ----------------------------------------------
SELECT
  (SUM(CASE WHEN score >= 9 THEN 1 ELSE 0 END)*100.0 / COUNT(*)) -
  (SUM(CASE WHEN score <= 6 THEN 1 ELSE 0 END)*100.0 / COUNT(*)) AS nps
FROM survey_responses;

SELECT
  SUM(CASE WHEN score >= 4 THEN 1 ELSE 0 END)*100.0 / COUNT(*) AS csat
FROM survey_responses;

COMMIT;


-- =============================================================
-- SECTION 5️⃣ : CLEANUP (SAFE DROP)
-- =============================================================

-- Drop all temporary or demo tables safely
DROP TABLE IF EXISTS raw_daily_sales CASCADE;
DROP TABLE IF EXISTS clean_daily_sales CASCADE;
DROP TABLE IF EXISTS kpi_daily_summary CASCADE;
DROP TABLE IF EXISTS kpi_region_summary CASCADE;

DROP TABLE IF EXISTS signups CASCADE;
DROP TABLE IF EXISTS subscriptions CASCADE;
DROP TABLE IF EXISTS events CASCADE;
DROP TABLE IF EXISTS purchases CASCADE;
DROP TABLE IF EXISTS survey_responses CASCADE;

-- SELECT 'Cleanup complete. All demo tables removed.' AS cleanup_status;

-- =============================================================
-- END OF FILE
-- =============================================================