NUHMAN.com

📘 Article Content:

In this article, we walk through two real-world examples of SQL use cases: analyzing product sales by date and summarizing high-volume product orders within a date range. These scenarios are commonly encountered in retail or e-commerce analytics.

📊 Part 1: Tracking Product Sales by Date

✅ Create and Prepare the Activities Table

First, we define a table Activities to capture each product sale along with its date:

sql

CREATE TABLE IF NOT EXISTS Activities (
    sell_date DATE, 
    product VARCHAR(20)
);
TRUNCATE TABLE Activities;

This ensures the table is clean before inserting new data.

🛒 Insert Sample Sales Data

sql

INSERT INTO Activities (sell_date, product) VALUES 
('2020-05-30', 'Headphone'),
('2020-06-01', 'Pencil'),
('2020-06-02', 'Mask'),
('2020-05-30', 'Basketball'),
('2020-06-01', 'Bible'),
('2020-06-02', 'Mask'),
('2020-05-30', 'T-Shirt');

This dataset includes multiple product sales on various dates, including duplicates like "Mask".

📈 Query: Unique Products Sold per Date

sql

SELECT 
    sell_date,
    COUNT(DISTINCT product) AS num_sold,
    GROUP_CONCAT(DISTINCT product ORDER BY product)
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;

Explanation:

• COUNT(DISTINCT product) gives the number of unique products sold on each date.
• GROUP_CONCAT(...) returns a comma-separated list of those products, sorted alphabetically.
• GROUP BY and ORDER BY organize the output by sell_date.

Use Case: This query is useful for understanding daily product diversity in sales.

📦 Part 2: Analyzing Product Orders by Category and Volume

✅ Drop and Recreate Product & Order Tables

sql

DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Orders;

CREATE TABLE IF NOT EXISTS Products (
    product_id INT, 
    product_name VARCHAR(40), 
    product_category VARCHAR(40)
);

CREATE TABLE IF NOT EXISTS Orders (
    product_id INT, 
    order_date DATE, 
    unit INT
);

TRUNCATE TABLE Products;
TRUNCATE TABLE Orders;

We clean up any previous data and recreate two tables: one for product details and one for order records.

📥 Insert Product and Order Data

Products Table:

sql

INSERT INTO Products (product_id, product_name, product_category) VALUES 
(1, 'Leetcode Solutions', 'Book'),
(2, 'Jewels of Stringology', 'Book'),
(3, 'HP', 'Laptop'),
(4, 'Lenovo', 'Laptop'),
(5, 'Leetcode Kit', 'T-shirt');

Orders Table:

sql

INSERT INTO Orders (product_id, order_date, unit) VALUES 
(1, '2020-02-05', 60),
(1, '2020-02-10', 70),
(2, '2020-01-18', 30),
(2, '2020-02-11', 80),
(3, '2020-02-17', 2),
(3, '2020-02-24', 3),
(4, '2020-03-01', 20),
(4, '2020-03-04', 30),
(4, '2020-03-04', 60),
(5, '2020-02-25', 50),
(5, '2020-02-27', 50),
(5, '2020-03-01', 50);

This dataset includes multiple orders for each product on different dates.

📊 Query: List Products Ordered ≥ 100 Units in February 2020

sql

SELECT 
    p.product_name, 
    o.unit
FROM (
    SELECT product_id, SUM(unit) AS unit
    FROM Orders
    WHERE order_date BETWEEN '2020-02-01' AND '2020-02-29'
    GROUP BY product_id
    HAVING unit >= 100
) o
INNER JOIN Products p ON o.product_id = p.product_id;

Explanation:

• The subquery calculates the total units ordered per product in February 2020 and filters to only include products with ≥ 100 units sold.
• The main query joins this result with the Products table to show the product names.

Use Case: Useful for inventory or supply chain teams to identify high-demand products in a time window.

🧠 Final Thoughts

These examples illustrate practical SQL for:

• Summarizing product activity over time
• Aggregating and filtering sales based on conditions
• Joining tables to enrich query results with meaningful data

SQL remains a powerful tool for data analytics, especially when you structure your queries to break problems down into logical steps like aggregation, filtering, and joining.