Ranking and Movement Analysis with Window Functions

from google.cloud import bigquery
import pandas as pd
 
# Authentication setup (when using service account key)
# import os
# os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/your-key.json"
 
# Create BigQuery client
client = bigquery.Client(project='your-project-id')
 
# Query execution function
def run_query(query):
    return client.query(query).to_dataframe()

function_name() OVER (
    [PARTITION BY group_column]     -- Specify grouping unit (optional)
    [ORDER BY sort_column]          -- Sort criteria (required for some functions)
    [ROWS/RANGE frame_specification] -- Window range (optional)
)

WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        p.brand,
        SUM(oi.sale_price) AS total_revenue
    FROM `your-project-id.thelook_ecommerce.order_items` oi
    JOIN `your-project-id.thelook_ecommerce.products` p ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name, p.category, p.brand
)
SELECT
    category,
    name,
    brand,
    total_revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC) AS rank_in_category
FROM product_revenue
QUALIFY rank_in_category <= 3
ORDER BY category, rank_in_category

LAG(column, offset, default) OVER (PARTITION BY ... ORDER BY ...)

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC(CAST(o.created_at AS DATE), MONTH) AS month,
        SUM(oi.sale_price) AS revenue
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    GROUP BY month
)
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS revenue_change,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month)) /
        LAG(revenue, 1) OVER (ORDER BY month) * 100,
        2
    ) AS growth_rate_pct
FROM monthly_revenue
WHERE month >= '2024-01-01'
ORDER BY month

SUM(col) OVER (
    ORDER BY date
    ROWS BETWEEN start AND end
)

WITH daily_revenue AS (
    SELECT
        CAST(o.created_at AS DATE) AS date,
        SUM(oi.sale_price) AS revenue
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    GROUP BY date
)
SELECT
    date,
    revenue,
    -- Cumulative sum
    SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS cumulative_revenue,
    -- 7-day moving average
    ROUND(
        AVG(revenue) OVER (
            ORDER BY date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7d
FROM daily_revenue
WHERE date >= '2024-10-01'
ORDER BY date

SELECT
    o.user_id,
    o.order_id,
    o.created_at,
    oi.sale_price,
    -- Purchase order by customer
    ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS purchase_order,
    -- Cumulative purchase amount by customer
    SUM(oi.sale_price) OVER (
        PARTITION BY o.user_id
        ORDER BY o.created_at
        ROWS UNBOUNDED PRECEDING
    ) AS cumulative_purchase,
    -- Days since last purchase
    DATE_DIFF(
        CAST(o.created_at AS DATE),
        CAST(LAG(o.created_at) OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS DATE),
        DAY
    ) AS days_since_last_purchase
FROM `your-project-id.thelook_ecommerce.orders` o
JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
WHERE o.user_id IN (1, 2, 3)  -- Sample customers
ORDER BY o.user_id, o.created_at