Complex Queries & Reports
Write production-level SQL queries — multi-table JOINs, subqueries, window functions, and business intelligence reports for the e-commerce platform.
55 min•By Priygop Team•Last updated: Feb 2026
Business Queries
- Top Customers: SELECT u.first_name, u.last_name, COUNT(o.id) AS order_count, SUM(o.total) AS lifetime_value FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status != 'cancelled' GROUP BY u.id ORDER BY lifetime_value DESC LIMIT 10 — find highest-value customers
- Revenue by Category: SELECT c.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue, COUNT(DISTINCT o.id) AS order_count FROM categories c JOIN products p ON c.id = p.category_id JOIN order_items oi ON p.id = oi.product_id JOIN orders o ON oi.order_id = o.id GROUP BY c.id ORDER BY revenue DESC
- Monthly Revenue: SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS orders, SUM(total) AS revenue, AVG(total) AS avg_order_value FROM orders WHERE status = 'delivered' GROUP BY month ORDER BY month — time-series revenue analysis
- Low Stock Alert: SELECT name, sku, stock FROM products WHERE is_active = true AND stock < 10 ORDER BY stock ASC — products running low. In production, automate this as a daily notification
- Abandoned Carts: SELECT u.email, c.created_at, COUNT(ci.id) AS items, SUM(ci.quantity * p.price) AS cart_value FROM carts c JOIN cart_items ci ON c.id = ci.cart_id JOIN products p ON ci.product_id = p.id JOIN users u ON c.user_id = u.id WHERE c.updated_at < NOW() - INTERVAL '24 hours' GROUP BY u.email, c.created_at — identify potential re-engagement targets
- Product Performance: SELECT p.name, SUM(oi.quantity) AS units_sold, SUM(oi.quantity * oi.unit_price) AS revenue, ROUND(AVG(r.rating), 1) AS avg_rating FROM products p LEFT JOIN order_items oi ON p.id = oi.product_id LEFT JOIN reviews r ON p.id = r.product_id GROUP BY p.id ORDER BY revenue DESC — combine sales data with ratings
Advanced Query Techniques
- Window Functions: SELECT name, price, category_id, RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank FROM products — rank products within each category without GROUP BY collapsing rows
- Running Total: SELECT created_at, total, SUM(total) OVER (ORDER BY created_at) AS running_revenue FROM orders WHERE status = 'delivered' — cumulative revenue over time for growth charts
- Subquery in WHERE: SELECT * FROM products WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items) — find products that have never been ordered. Useful for inventory cleanup decisions
- CTE (Common Table Expression): WITH monthly_stats AS (SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue FROM orders GROUP BY month) SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_month, ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 1) AS growth_pct FROM monthly_stats — month-over-month growth rate
- CASE Expressions: SELECT name, price, CASE WHEN price < 25 THEN 'Budget' WHEN price < 100 THEN 'Mid-Range' WHEN price < 500 THEN 'Premium' ELSE 'Luxury' END AS price_tier FROM products — categorize data dynamically without separate tables
- COALESCE & NULL Handling: SELECT p.name, COALESCE(SUM(oi.quantity), 0) AS units_sold, COALESCE(ROUND(AVG(r.rating), 1), 0) AS avg_rating FROM products p LEFT JOIN order_items oi ON p.id = oi.product_id LEFT JOIN reviews r ON p.id = r.product_id GROUP BY p.id — replace NULL with 0 for products with no sales/reviews