SQL Queries E-Commerce Analysis

Top Spending Customers (Scalar Subquery)

Write a query to find all customers who have spent more than the average total order amount. Use a scalar subquery in the WHERE clause.

• Calculate total spending per customer from the orders table
• Compare each customer's total to the average using a scalar subquery
• Display customer name, email, total spending, and average spending
• Order by total spending in descending order

-- Expected columns: customer_name, email, total_spent, avg_spending
-- Must use: Scalar subquery for calculating average

Products Never Ordered (Table Subquery)

Find all products that have never been ordered using a table subquery with NOT IN or NOT EXISTS.

• Use a subquery to get all product_id values from order_items
• Return products NOT IN that list
• Display product name, category name, unit price, and stock quantity
• Include a LEFT JOIN with categories table

-- Expected columns: product_name, category_name, unit_price, stock_quantity
-- Must use: NOT IN with subquery or NOT EXISTS

Most Recent Order per Customer (Correlated Subquery)

For each customer, find their most recent order details using a correlated subquery.

• Use a correlated subquery in WHERE clause with MAX(order_date)
• Display customer name, order date, order status, and total amount
• Only include customers who have placed at least one order
• Order by order date descending

-- Expected columns: customer_name, order_date, order_status, total_amount
-- Must use: Correlated subquery comparing order_date to MAX(order_date)

Monthly Revenue Analysis (Single CTE)

Calculate monthly revenue trends for 2024 using a CTE to organize the query logic.

• Create a CTE called monthly_sales that aggregates order totals by month
• Use EXTRACT function to get month from order_date
• Calculate total revenue, number of orders, and average order value per month
• In main query, display month name, revenue, order count, and avg order value

-- Expected columns: month_name, total_revenue, order_count, avg_order_value
-- Must use: WITH clause for CTE, EXTRACT function, aggregate functions

Customer Segmentation (Multiple CTEs)

Segment customers into spending tiers (High, Medium, Low) using multiple CTEs.

• CTE 1: customer_spending calculates total spent per customer
• CTE 2: spending_thresholds calculates 33rd and 66th percentiles
• Main query: Use CASE to classify customers based on thresholds
• Display customer name, total spent, and spending tier

-- Expected columns: customer_name, total_spent, spending_tier
-- Must use: Multiple CTEs with WITH clause, PERCENTILE_CONT, CASE statement

Product Performance Hierarchy (Nested CTEs)

Analyze product performance within each category using nested CTEs.

• CTE 1: product_sales calculates total revenue per product
• CTE 2: category_totals calculates total revenue per category
• CTE 3: product_contribution calculates each product's contribution percentage
• Display category, product name, product revenue, and contribution percentage
• Only show products contributing more than 10% to their category

-- Expected columns: category_name, product_name, product_revenue, contribution_pct
-- Must use: 3 nested CTEs, percentage calculation, filtering

Top 5 Products per Category (ROW_NUMBER)

Rank products within each category by total revenue and return the top 5 per category.

• Calculate total revenue for each product (quantity * unit_price * discount)
• Use ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC)
• Filter to only include rows where rank is less than or equal to 5
• Display category name, product name, revenue, and rank

-- Expected columns: category_name, product_name, total_revenue, product_rank
-- Must use: ROW_NUMBER window function with PARTITION BY

Customer Order Frequency Ranking (RANK and DENSE_RANK)

Rank customers by number of orders placed, showing both RANK and DENSE_RANK for comparison.

• Count total orders per customer
• Apply RANK() and DENSE_RANK() ordered by order count descending
• Display customer name, order count, rank, and dense rank
• Show top 20 customers only

-- Expected columns: customer_name, order_count, rank, dense_rank
-- Must use: Both RANK() and DENSE_RANK() window functions

Running Total Revenue (Running Sum)

Calculate a running total of revenue by order date for the year 2024.

• Group orders by date (use DATE function to extract date from timestamp)
• Calculate daily revenue totals
• Use SUM() OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
• Display date, daily revenue, and running total

-- Expected columns: order_date, daily_revenue, running_total
-- Must use: Window function with ROWS frame for running total

Customer Order Patterns (LAG and LEAD)

Analyze customer purchasing patterns by comparing current order date with previous and next order dates.

• For each customer's orders, get previous order date using LAG()
• Get next order date using LEAD()
• Calculate days since last order and days until next order
• Partition by customer_id and order by order_date
• Display customer name, order date, previous date, next date, days gaps

-- Expected columns: customer_name, order_date, prev_order_date, next_order_date, days_since_last, days_until_next
-- Must use: LAG() and LEAD() window functions with PARTITION BY

Order Processing Time Analysis (Date Arithmetic)

Calculate average processing time (ship_date - order_date) for each order status.

• Use DATE_PART or EXTRACT to calculate days between order_date and ship_date
• Calculate AVG processing time for each order_status
• Only include orders where ship_date is NOT NULL
• Display order status, average processing days, and order count

-- Expected columns: order_status, avg_processing_days, order_count
-- Must use: Date arithmetic functions (DATE_PART or EXTRACT)

Seasonal Sales Analysis (Date Extraction)

Analyze sales by season (Winter, Spring, Summer, Fall) for 2024.

• Use EXTRACT(MONTH FROM order_date) to get month
• Use CASE to map months to seasons (Dec-Feb: Winter, Mar-May: Spring, Jun-Aug: Summer, Sep-Nov: Fall)
• Calculate total revenue and order count per season
• Display season, total revenue, order count, and average order value

-- Expected columns: season, total_revenue, order_count, avg_order_value
-- Must use: EXTRACT function, CASE statement for season mapping

Customer Name Formatting (String Functions)

Format customer names for email campaigns using string manipulation functions.

• Create full_name as "Last Name, First Name" using CONCAT
• Create email_greeting as "Dear FirstName" using CONCAT and INITCAP
• Extract domain from email using SUBSTRING and POSITION
• Display formatted name, email greeting, email, and email domain

-- Expected columns: full_name, email_greeting, email, email_domain
-- Must use: CONCAT, INITCAP, SUBSTRING, POSITION functions

High-Value Transactions Report (UNION ALL)

Combine high-value orders and high-value payments into a single report using UNION ALL.

• Query 1: Select orders with total_amount greater than $1000
• Query 2: Select payments with payment_amount greater than $1000
• Use UNION ALL to combine both result sets
• Display transaction_type (Order or Payment), transaction_id, amount, date

-- Expected columns: transaction_type, transaction_id, amount, transaction_date
-- Must use: UNION ALL to combine two SELECT statements

Customer Contact List (UNION with Deduplication)

Create a master contact list combining customers from different regions, removing duplicates.

• Query 1: Select customers from California with email
• Query 2: Select customers from Texas with email
• Query 3: Select customers from New York with email
• Use UNION (not UNION ALL) to automatically deduplicate
• Display customer name, email, state, and registration date
• Order by registration date descending

-- Expected columns: customer_name, email, state, registration_date
-- Must use: UNION (without ALL) for automatic deduplication

Create SQL Script File

Create ecommerce_analysis.sql containing:

• All 15 required queries organized by part (Subqueries, CTEs, Window Functions, etc.)
• Clear comments above each query explaining its purpose
• Section headers separating different parts
• Proper SQL formatting and indentation

-- ================================================================
-- PART 1: SUBQUERIES (30 POINTS)
-- ================================================================

-- Query 1: Top Spending Customers
-- Purpose: Find customers who spent more than average
SELECT ...

-- Query 2: Products Never Ordered
-- Purpose: Identify products with zero orders
SELECT ...

Create README File

Create README.md that includes:

• Your name and assignment title
• Brief description of the ShopSphere database
• Instructions to set up the database and run queries
• List of all 15 queries with brief descriptions
• Key insights discovered from your analysis
• Any challenges faced and how you solved them

Export Query Results

Create a folder called results/ containing:

• CSV exports of at least 5 key query results
• Name files descriptively (e.g., top_spending_customers.csv)
• Include column headers in CSV files

# Export query results to CSV
psql shopsphere_db -c "COPY (SELECT ...) TO '/path/to/results/query1.csv' CSV HEADER;"

Create Documentation File

Create INSIGHTS.md that documents:

• Top 3 business insights discovered from your analysis
• Actionable recommendations for ShopSphere based on data
• Interesting patterns or trends you identified
• Any data quality issues you noticed

Repository Structure

Your GitHub repository should look like this:

shopsphere-sql-analysis/
├── README.md
├── ecommerce_analysis.sql    # All 15 queries with comments
├── INSIGHTS.md               # Business insights and recommendations
└── results/
    ├── top_spending_customers.csv
    ├── monthly_revenue.csv
    ├── product_rankings.csv
    ├── customer_patterns.csv
    └── seasonal_sales.csv

Submit via Form

Once your repository is ready:

• Make sure your repository is public or shared with your instructor
• Verify all queries execute without errors in PostgreSQL
• Click the "Submit Assignment" button below
• Fill in the submission form with your GitHub repository URL