Building Two SQL Dashboards: E-commerce Analytics + Student Performance Reporting

Sourav Sarkar — Wed, 26 Nov 2025 10:57:49 +0000

Over the past week, I worked on two hands-on SQL projects as part of my learning journey. Both projects strengthened my analytical skills and helped me understand how SQL powers real-world decision-making.

This blog covers:

E-commerce Analytics Dashboard (revenue, top products, customer behavior)

Student Management Dashboard (averages, top scorers, subject difficulty)

I’m sharing my queries, approach, and key findings so others learning SQL can see how business insights come together using joins, aggregations, window functions, and time-series logic.

🛒 Project 1: E-commerce Analytics Dashboard (SQL)

The goal was to understand customer behavior, product performance, and revenue trends using an e-commerce dataset consisting of:

customers
products
orders
order_items

Below are the insights I generated.

📈 1. Monthly Revenue Trend (Last 24 Months)

I used DATE_TRUNC() to group sales by month and track revenue over the last two years.

SELECT
    TO_CHAR(DATE_TRUNC('month', o.order_date), 'Month YYYY') AS month_label,
    DATE_TRUNC('month', o.order_date) AS month_start,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    COUNT(*) AS total_orders
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_date >= NOW() - INTERVAL '24 months'
GROUP BY month_start
ORDER BY month_start;

🔍 What it tells us

Month-over-month revenue changes
Growth direction for the business

📸 Screenshot :

🔄 2. Customers Whose Orders Increased from 2024 → 2025

This query compares order counts across years to identify fast-growing customers.

select 
c.name,c.customer_id,
sum(case when extract(year from o.order_date) = 2025 then 1 else 0 end) as year_date_2025,
sum(case when extract(year from o.order_date) = 2024 then 1 else 0 end) as year_date_2024
from customers as c
join orders as o on o.customer_id  = c.customer_id
group by c.name,c.customer_id
having 
      sum(case when extract(year from o.order_date) = 2025 then 1 end)
    > sum(case when extract(year from o.order_date) = 2024 then 1 end)
   AND 
      sum(case when extract(year from o.order_date) = 2025 then 1 end) > 0
   AND
      sum(case when extract(year from o.order_date) = 2024 then 1 end) > 0;

🔍 What it tells us

Which customers have become more active
A simple year-over-year loyalty signal
Helps identify customers worth targeting for retention campaigns

📸 Screenshot:

🏆 3. Top 3 Products in Each Category (Window Function)

Using ROW_NUMBER() to rank items within each category:

select * from (
    select 
        p.name as product_name, 
        p.category,
        p.product_id,
        sum(ot.quantity * ot.unit_price) as total_sales,
        row_number() over(
            partition by p.category
            order by sum(ot.quantity * ot.unit_price) desc
        ) as rn
    from products as p
    join order_items as ot on ot.product_id = p.product_id
    group by p.category,p.name,p.product_id
) x 
where rn <= 3
order by category, total_sales desc;

🔍 What it tells us

The top performers inside each category
Helps identify products to promote or bundle

📸 Screenshot:

🛍️ 4. Top 10 Best-Selling Products (By Quantity)

select 
products.product_id,
products.name as product_name,
sum(order_items.unit_price * order_items.quantity) as total_sale,
sum(order_items.quantity) as quantity
from products
join order_items on order_items.product_id = products.product_id
group by products.product_id
order by total_sale desc
limit 10;

🔍 Key insight

1. These are your highest-demand products
2. Useful for stock planning and reorder frequency

💰 5. Top 15 Revenue Generating Products

select 
products.product_id as product_id,
products.name as product_name,
sum(order_items.quantity) as total_units_sold,
round(avg(order_items.unit_price),2) as avg_unit_price,
sum(order_items.quantity * order_items.unit_price) as total_revenue
from products
join order_items on order_items.product_id = products.product_id
group by products.product_id
order by total_revenue desc
limit 15;

🔍 Key insight

These products make the biggest contribution to revenue

📸 Screenshot :

🧩 Other Insights (from my additional queries)

Average Order Value per month → financial health indicator
Category performance → which category earns the most
One-time vs repeat customers → customer segmentation
Top spenders → lifetime value leaderboard

Together, these form a complete e-commerce analytics dashboard using SQL.

🎓 Project 2: Student Management Dashboard (SQL)

This project focused on analyzing academic performance using tables:

students
subjects
scores

The goal was to create insights similar to what a school dashboard would show.

📊 1. Student Performance Summary

Using AVG, MIN, MAX, and a CASE statement to create a “performance review”.

select 
s.name ,
round(avg(sc.score),2) as avg_score,
min(sc.score) as min_score,
max(sc.score) as max_score,
case 
   when round(avg(sc.score),2) > 95 then 'Academically Blessed'
   when round(avg(sc.score),2) > 90 then 'Top Performer'
   when round(avg(sc.score),2) > 80 then 'Above Average'
   when round(avg(sc.score),2) > 70 then 'Average'
   when round(avg(sc.score),2) > 55 then 'Needs Improvement'
   else 'Failing'
end as performance_review
from scores as sc
join students as s on s.student_id = sc.student_id
group by s.name;

🔍 What it tells us

Each student's average
Performance category
Range of scores

📸 Screenshot:

🏅 2. Top Performers per Subject

SELECT 
    sub.subject_name,
    s.name AS top_student,
    sc.score AS top_score
FROM scores sc
JOIN subjects sub ON sub.subject_id = sc.subject_id
JOIN students s ON s.student_id = sc.student_id
WHERE sc.score = (
    SELECT MAX(score)
    FROM scores sc2
    WHERE sc2.subject_id = sc.subject_id
)
ORDER BY sub.subject_name;

🔍 Key insight

Highlights subject toppers

🎯 3. Subject Difficulty Overview

select sub.subject_name,
       round(avg(sc.score),2) as avg_mark,
       max(sc.score) as max_marks,
       min(sc.score) as min_marks,
       (sum(case when sc.score > 50 then 1 end) * 100 / count(*)) as passing_rate
from subjects as sub
join scores sc on sc.subject_id = sub.subject_id
group by sub.subject_name
order by passing_rate;

🔍 What it tells us

Which subjects are easiest/hardest
Passing rate trend
Score distribution visibility

📸 Screenshot placeholder:

What I Learned from These Two Projects

Window functions (ROW_NUMBER)
Time-series analysis (DATE_TRUNC, TO_CHAR)
Business metrics like AOV, revenue, LTV
CASE expressions for segmentation
Joining multiple tables to build dashboards
Thinking like an analyst, not just writing queries

Both projects gave me real-world experience in building analytics dashboards purely using SQL.

Final Thoughts

These two projects helped me understand how SQL is used in:

E-commerce analytics
Education dashboards
KPI tracking
Ranking and segmentation
Time-based reporting

If you're also learning SQL, try replicating these dashboards on any dataset you can find — it’s a great way to level up your analytical thinking. Feedbacks are appreciated

Data sets used

ecommerce

students

DEV Community: Sourav Sarkar

Building Two SQL Dashboards: E-commerce Analytics + Student Performance Reporting