05_bigquery_examples.sql

-- BigQuery Public Dataset Examples
-- Adapted queries for Google BigQuery's The Look E-commerce dataset
-- Dataset: bigquery-public-data.thelook_ecommerce
-- Free tier: 1 TB queries/month

-- ============================================================================
-- SETUP: Explore the dataset structure first
-- ============================================================================

-- Check available tables and their schemas
SELECT 
  table_name,
  column_name,
  data_type,
  is_nullable
FROM `bigquery-public-data.thelook_ecommerce.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name IN ('users', 'orders', 'order_items', 'products')
ORDER BY table_name, ordinal_position;

-- ============================================================================
-- JOINS WITH BIGQUERY SYNTAX
-- ============================================================================

-- 1. INNER JOIN: Orders with customer details (BigQuery)
-- Note: BigQuery uses backticks for table names and CONCAT instead of ||
SELECT 
    o.order_id,
    o.created_at AS order_date,
    CONCAT(u.first_name, ' ', u.last_name) AS customer_name,
    u.email,
    o.status
FROM `bigquery-public-data.thelook_ecommerce.orders` o
INNER JOIN `bigquery-public-data.thelook_ecommerce.users` u 
    ON o.user_id = u.id
WHERE o.status = 'Complete'  -- Filter for completed orders only
ORDER BY o.created_at DESC
LIMIT 100;  -- BigQuery best practice: Always use LIMIT for exploration

-- 2. LEFT JOIN: All users and their order counts
SELECT 
    u.id AS customer_id,
    CONCAT(u.first_name, ' ', u.last_name) AS customer_name,
    u.email,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END) AS has_orders
FROM `bigquery-public-data.thelook_ecommerce.users` u
LEFT JOIN `bigquery-public-data.thelook_ecommerce.orders` o 
    ON u.id = o.user_id AND o.status = 'Complete'
GROUP BY u.id, u.first_name, u.last_name, u.email
ORDER BY total_orders DESC
LIMIT 100;

-- 3. MULTIPLE JOINS: Detailed order information
SELECT 
    o.order_id,
    DATE(o.created_at) AS order_date,
    CONCAT(u.first_name, ' ', u.last_name) AS customer_name,
    p.name AS product_name,
    p.category AS product_category,
    oi.sale_price AS unit_price,
    oi.num_of_item AS quantity,
    (oi.sale_price * oi.num_of_item) AS line_total
FROM `bigquery-public-data.thelook_ecommerce.orders` o
INNER JOIN `bigquery-public-data.thelook_ecommerce.users` u 
    ON o.user_id = u.id
INNER JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi 
    ON o.order_id = oi.order_id
INNER JOIN `bigquery-public-data.thelook_ecommerce.products` p 
    ON oi.product_id = p.id
WHERE o.status = 'Complete'
ORDER BY o.created_at DESC
LIMIT 1000;

-- ============================================================================
-- CTEs WITH BIGQUERY SYNTAX
-- ============================================================================

-- 4. CTE: Calculate order totals (BigQuery)
WITH order_totals AS (
    -- Calculate total value for each completed order
    SELECT 
        o.order_id,
        o.user_id AS customer_id,
        DATE(o.created_at) AS order_date,
        SUM(oi.sale_price * oi.num_of_item) AS order_total
    FROM `bigquery-public-data.thelook_ecommerce.orders` o
    INNER JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi 
        ON o.order_id = oi.order_id
    WHERE o.status = 'Complete'
    GROUP BY o.order_id, o.user_id, DATE(o.created_at)
)
SELECT 
    ot.order_id,
    ot.order_date,
    CONCAT(u.first_name, ' ', u.last_name) AS customer_name,
    ROUND(ot.order_total, 2) AS order_total
FROM order_totals ot
INNER JOIN `bigquery-public-data.thelook_ecommerce.users` u 
    ON ot.customer_id = u.id
ORDER BY ot.order_total DESC
LIMIT 100;

-- ============================================================================
-- WINDOW FUNCTIONS WITH BIGQUERY SYNTAX
-- ============================================================================

-- 5. ROW_NUMBER: Rank orders by value within each customer
SELECT 
    o.order_id,
    CONCAT(u.first_name, ' ', u.last_name) AS customer_name,
    DATE(o.created_at) AS order_date,
    SUM(oi.sale_price * oi.num_of_item) AS order_total,
    ROW_NUMBER() OVER (
        PARTITION BY o.user_id 
        ORDER BY SUM(oi.sale_price * oi.num_of_item) DESC
    ) AS order_rank
FROM `bigquery-public-data.thelook_ecommerce.orders` o
INNER JOIN `bigquery-public-data.thelook_ecommerce.users` u 
    ON o.user_id = u.id
INNER JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi 
    ON o.order_id = oi.order_id
WHERE o.status = 'Complete'
GROUP BY o.order_id, u.first_name, u.last_name, DATE(o.created_at), o.user_id
ORDER BY u.last_name, order_rank
LIMIT 100;

-- 6. LAG: Month-over-month revenue comparison
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC(DATE(o.created_at), MONTH) AS month,
        SUM(oi.sale_price * oi.num_of_item) AS monthly_total
    FROM `bigquery-public-data.thelook_ecommerce.orders` o
    INNER JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi 
        ON o.order_id = oi.order_id
    WHERE o.status = 'Complete'
    GROUP BY DATE_TRUNC(DATE(o.created_at), MONTH)
)
SELECT 
    month,
    ROUND(monthly_total, 2) AS monthly_revenue,
    ROUND(LAG(monthly_total) OVER (ORDER BY month), 2) AS previous_month,
    ROUND(monthly_total - LAG(monthly_total) OVER (ORDER BY month), 2) AS month_over_month_change,
    ROUND(
        (monthly_total - LAG(monthly_total) OVER (ORDER BY month)) / 
        NULLIF(LAG(monthly_total) OVER (ORDER BY month), 0) * 100, 
        2
    ) AS growth_rate_pct
FROM monthly_revenue
ORDER BY month;

-- ============================================================================
-- BUSINESS QUESTIONS ADAPTED FOR BIGQUERY
-- ============================================================================

-- Q1: Top 10 Customers by Total Revenue (BigQuery)
WITH customer_revenue AS (
    SELECT 
        u.id AS customer_id,
        CONCAT(u.first_name, ' ', u.last_name) AS customer_name,
        u.email,
        DATE(u.created_at) AS registration_date,
        SUM(oi.sale_price * oi.num_of_item) AS total_revenue,
        COUNT(DISTINCT o.order_id) AS order_count,
        AVG(oi.sale_price * oi.num_of_item) AS avg_order_value
    FROM `bigquery-public-data.thelook_ecommerce.users` u
    INNER JOIN `bigquery-public-data.thelook_ecommerce.orders` o 
        ON u.id = o.user_id
    INNER JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi 
        ON o.order_id = oi.order_id
    WHERE o.status = 'Complete'
    GROUP BY u.id, u.first_name, u.last_name, u.email, DATE(u.created_at)
)
SELECT 
    customer_name,
    email,
    registration_date,
    ROUND(total_revenue, 2) AS total_revenue,
    order_count,
    ROUND(avg_order_value, 2) AS avg_order_value,
    RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM customer_revenue
ORDER BY total_revenue DESC
LIMIT 10;

-- Q2: Monthly Revenue Trend (BigQuery)
SELECT 
    DATE_TRUNC(DATE(o.created_at), MONTH) AS month,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COUNT(DISTINCT o.user_id) AS unique_customers,
    SUM(oi.num_of_item) AS total_items_sold,
    ROUND(SUM(oi.sale_price * oi.num_of_item), 2) AS monthly_revenue,
    ROUND(AVG(oi.sale_price * oi.num_of_item), 2) AS avg_order_value,
    ROUND(
        LAG(SUM(oi.sale_price * oi.num_of_item)) OVER (
            ORDER BY DATE_TRUNC(DATE(o.created_at), MONTH)
        ), 
        2
    ) AS previous_month_revenue,
    ROUND(
        SUM(oi.sale_price * oi.num_of_item) - 
        LAG(SUM(oi.sale_price * oi.num_of_item)) OVER (
            ORDER BY DATE_TRUNC(DATE(o.created_at), MONTH)
        ), 
        2
    ) AS revenue_change
FROM `bigquery-public-data.thelook_ecommerce.orders` o
INNER JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi 
    ON o.order_id = oi.order_id
WHERE o.status = 'Complete'
GROUP BY DATE_TRUNC(DATE(o.created_at), MONTH)
ORDER BY month;

-- Q3: Customer Retention Analysis (BigQuery)
-- Note: BigQuery uses DATE_DIFF instead of DATEDIFF
WITH customer_cohorts AS (
    SELECT 
        u.id AS customer_id,
        DATE_TRUNC(DATE(u.created_at), MONTH) AS cohort_month,
        DATE_TRUNC(DATE(o.created_at), MONTH) AS order_month
    FROM `bigquery-public-data.thelook_ecommerce.users` u
    LEFT JOIN `bigquery-public-data.thelook_ecommerce.orders` o 
        ON u.id = o.user_id AND o.status = 'Complete'
    GROUP BY u.id, DATE_TRUNC(DATE(u.created_at), MONTH), DATE_TRUNC(DATE(o.created_at), MONTH)
),
cohort_analysis AS (
    SELECT 
        cohort_month,
        order_month,
        COUNT(DISTINCT customer_id) AS customers,
        DATE_DIFF(order_month, cohort_month, MONTH) AS period_number
    FROM customer_cohorts
    WHERE order_month IS NOT NULL
    GROUP BY cohort_month, order_month, DATE_DIFF(order_month, cohort_month, MONTH)
),
cohort_sizes AS (
    SELECT 
        cohort_month,
        COUNT(DISTINCT customer_id) AS cohort_size
    FROM customer_cohorts
    GROUP BY cohort_month
)
SELECT 
    ca.cohort_month,
    ca.period_number,
    cs.cohort_size,
    ca.customers AS retained_customers,
    ROUND(SAFE_DIVIDE(ca.customers, cs.cohort_size) * 100, 2) AS retention_rate_pct
FROM cohort_analysis ca
INNER JOIN cohort_sizes cs ON ca.cohort_month = cs.cohort_month
ORDER BY ca.cohort_month, ca.period_number;

-- ============================================================================
-- BIGQUERY-SPECIFIC FEATURES
-- ============================================================================

-- Using SAFE_DIVIDE to avoid division by zero errors
SELECT 
    product_id,
    total_revenue,
    total_orders,
    SAFE_DIVIDE(total_revenue, total_orders) AS avg_revenue_per_order
FROM (
    SELECT 
        oi.product_id,
        SUM(oi.sale_price * oi.num_of_item) AS total_revenue,
        COUNT(DISTINCT oi.order_id) AS total_orders
    FROM `bigquery-public-data.thelook_ecommerce.order_items` oi
    INNER JOIN `bigquery-public-data.thelook_ecommerce.orders` o 
        ON oi.order_id = o.order_id
    WHERE o.status = 'Complete'
    GROUP BY oi.product_id
)
ORDER BY total_revenue DESC
LIMIT 20;

-- Using ARRAY_AGG for product lists (BigQuery-specific)
SELECT 
    o.order_id,
    DATE(o.created_at) AS order_date,
    CONCAT(u.first_name, ' ', u.last_name) AS customer_name,
    SUM(oi.sale_price * oi.num_of_item) AS order_total,
    ARRAY_AGG(p.name ORDER BY oi.sale_price DESC LIMIT 5) AS top_products_in_order
FROM `bigquery-public-data.thelook_ecommerce.orders` o
INNER JOIN `bigquery-public-data.thelook_ecommerce.users` u 
    ON o.user_id = u.id
INNER JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi 
    ON o.order_id = oi.order_id
INNER JOIN `bigquery-public-data.thelook_ecommerce.products` p 
    ON oi.product_id = p.id
WHERE o.status = 'Complete'
GROUP BY o.order_id, DATE(o.created_at), u.first_name, u.last_name
ORDER BY order_total DESC
LIMIT 50;

-- ============================================================================
-- PERFORMANCE TIPS FOR BIGQUERY
-- ============================================================================

-- 1. Always use LIMIT when exploring data
-- 2. Use DATE() function to extract date from TIMESTAMP
-- 3. Use DATE_TRUNC() for grouping by time periods
-- 4. Use SAFE_DIVIDE() instead of regular division to avoid errors
-- 5. Filter early with WHERE clauses before JOINs
-- 6. Use APPROX_COUNT_DISTINCT() for large datasets when exact count isn't needed

-- Example: Optimized query with early filtering
SELECT 
    DATE_TRUNC(DATE(o.created_at), MONTH) AS month,
    COUNT(DISTINCT o.order_id) AS orders,
    SUM(oi.sale_price * oi.num_of_item) AS revenue
FROM `bigquery-public-data.thelook_ecommerce.orders` o
INNER JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi 
    ON o.order_id = oi.order_id
WHERE o.status = 'Complete'  -- Filter early!
    AND DATE(o.created_at) >= '2022-01-01'  -- Filter early!
GROUP BY month
ORDER BY month;