🛒 Olist E-Commerce — SQL Sales & Customer Analysis

Joins customers → orders → payments and ranks customers by lifetime spend.

select
    c.customer_id,
    round(sum(o2.payment_value), 2) as total_spent,
    count(distinct o1.order_id) as num_orders
from customers c
inner join orders o1
    on c.customer_id = o1.customer_id
inner join order_payments o2
    on o1.order_id = o2.order_id
group by c.customer_id
order by total_spent desc
limit 10;

Groups payments by purchase month to build the full monthly revenue series.

select
    date_format(o1.order_purchase_timestamp, '%Y-%m') as month,
    format(sum(o2.payment_value), 0) as revenue
from orders o1
inner join order_payments o2
    on o1.order_id = o2.order_id
group by month
order by month;

Uses a CTE plus the LAG() window function to compare each month's revenue against the prior month.

with monthly_revenue as (
    select
        date_format(order_purchase_timestamp, '%Y-%m') as month,
        sum(op.payment_value) as revenue
    from orders o
    inner join order_payments op
        on o.order_id = op.order_id
    group by date_format(order_purchase_timestamp, '%Y-%m')
)
select
    month,
    revenue,
    lag(revenue) over (order by month) as previous_month_revenue,
    revenue - lag(revenue) over (order by month) as mom_change
from monthly_revenue
order by month;

Joins products to order items and aggregates revenue across all 74 categories.

select
    p.product_category_name as product_category,
    sum(o.price) as revenue
from products p
inner join order_items o
    on p.product_id = o.product_id
group by product_category
order by revenue desc;

A ranked CTE using ROW_NUMBER() partitioned by category surfaces each category's top 3 revenue-generating products.

with product_revenue as (
    select
        p.product_category_name as category,
        oi.product_id,
        sum(oi.price) as revenue
    from products p
    inner join order_items oi
        on p.product_id = oi.product_id
    group by p.product_category_name, oi.product_id
),
ranked_products as (
    select
        category,
        product_id,
        revenue,
        row_number() over (
            partition by category
            order by revenue desc
        ) as product_rank
    from product_revenue
)
select category, product_id, revenue, product_rank
from ranked_products
where product_rank <= 3
order by category, product_rank;

Segments every customer into Low / Medium / High spend tiers using CASE WHEN logic.

with customer_spending as (
    select
        o.customer_id,
        sum(op.payment_value) as total_spend
    from orders o
    inner join order_payments op
        on o.order_id = op.order_id
    group by o.customer_id
)
select
    customer_id,
    total_spend,
    case
        when total_spend < 100 then 'low'
        when total_spend < 500 then 'medium'
        else 'high'
    end as spend_tier
from customer_spending
order by total_spend desc;

Counts each customer's total orders via ROW_NUMBER() to classify them as repeat or one-time buyers.

with customer_orders as (
    select
        o.order_id,
        c.customer_unique_id,
        row_number() over (
            partition by c.customer_unique_id
            order by o.order_id
        ) as purchase_number
    from orders o
    inner join customers c
        on o.customer_id = c.customer_id
),
customer_purchase_counts as (
    select
        customer_unique_id,
        max(purchase_number) as total_purchase
    from customer_orders
    group by customer_unique_id
)
select
    case
        when total_purchase = 1 then 'one_time_buyer'
        else 'repeat_buyer'
    end as customer_type,
    count(*) as customer_count
from customer_purchase_counts
group by customer_type;

Uses SUM() OVER () to calculate each category's revenue as a percentage of total order-item revenue.

with category_revenue as (
    select
        p.product_category_name as category,
        sum(oi.price) as revenue
    from products p
    inner join order_items oi
        on p.product_id = oi.product_id
    group by p.product_category_name
)
select
    category,
    revenue,
    sum(revenue) over () as total_revenue,
    round(revenue / sum(revenue) over () * 100, 2) as percent_of_total
from category_revenue
order by revenue desc;