Basics

1. Table Schema (DDL)

-- USER table
CREATE TABLE [User] (
    userid INT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL
);

-- PRODUCT table
CREATE TABLE Product (
    productid INT IDENTITY(1,1) PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    price FLOAT NOT NULL
);

-- USER_PURCHASE table
CREATE TABLE User_Purchase (
    purchaseid INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL,
    user_id INT NOT NULL,
    purchase_date DATETIME NOT NULL DEFAULT GETDATE(),
    quantity INT NOT NULL,
    total_price FLOAT NOT NULL,

    CONSTRAINT FK_UserPurchase_Product
        FOREIGN KEY (product_id) REFERENCES Product(productid),

    CONSTRAINT FK_UserPurchase_User
        FOREIGN KEY (user_id) REFERENCES [User](userid)
);

Note:

• [User] is a reserved keyword in SQL Server, so it is wrapped in square brackets.

• IDENTITY(1,1) implements auto-increment behavior.

---

2. Insert Dummy Data (DML)

Users (3 records)

INSERT INTO [User] (username, city)
VALUES
('Arjun', 'Chennai'),
('Meera', 'Bangalore'),
('Rahul', 'Mumbai');

---

Products (5 records)

INSERT INTO Product (title, price)
VALUES
('Laptop', 75000.00),
('Wireless Mouse', 1200.00),
('Mechanical Keyboard', 4500.00),
('27-inch Monitor', 18000.00),
('USB-C Hub', 2500.00);

---

User Purchases (Sample transactions)

INSERT INTO User_Purchase (product_id, user_id, purchase_date, quantity, total_price)
VALUES
(1, 1, '2025-01-10', 1, 75000.00),
(2, 1, '2025-01-11', 2, 2400.00),
(3, 2, '2025-01-12', 1, 4500.00),
(4, 3, '2025-01-13', 1, 18000.00),
(5, 2, '2025-01-14', 3, 7500.00);

---

3. Optional Validation Query

SELECT 
    u.username,
    p.title,
    up.quantity,
    up.total_price,
    up.purchase_date
FROM User_Purchase up
JOIN [User] u ON up.user_id = u.userid
JOIN Product p ON up.product_id = p.productid;

---

Sample SQL

Below are five practical SELECT queries you can use to explore and “play” with the data. These are written for Microsoft SQL Server (T-SQL) and cover common analytical patterns.

---

1. List All Purchases with User and Product Details

Shows who bought what, when, and for how much.

SELECT
    up.purchaseid,
    u.username,
    u.city,
    p.title AS product_name,
    up.quantity,
    p.price AS unit_price,
    up.total_price,
    up.purchase_date
FROM User_Purchase up
JOIN [User] u ON up.user_id = u.userid
JOIN Product p ON up.product_id = p.productid
ORDER BY up.purchase_date DESC;

---

2. Total Spending per User

Useful for customer value or ranking users by spend.

SELECT
    u.userid,
    u.username,
    u.city,
    SUM(up.total_price) AS total_spent
FROM [User] u
JOIN User_Purchase up ON u.userid = up.user_id
GROUP BY u.userid, u.username, u.city
ORDER BY total_spent DESC;

---

3. Product Sales Summary (Quantity + Revenue)

Helps identify best-selling products.

SELECT
    p.productid,
    p.title,
    SUM(up.quantity) AS total_units_sold,
    SUM(up.total_price) AS total_revenue
FROM Product p
JOIN User_Purchase up ON p.productid = up.product_id
GROUP BY p.productid, p.title
ORDER BY total_revenue DESC;

---

4. Users Who Purchased More Than One Item in a Single Order

Demonstrates filtering with business logic.

SELECT
    u.username,
    p.title,
    up.quantity,
    up.total_price
FROM User_Purchase up
JOIN [User] u ON up.user_id = u.userid
JOIN Product p ON up.product_id = p.productid
WHERE up.quantity > 1;

---

5. Daily Revenue Trend

Good for simple time-series analysis.

SELECT
    CAST(purchase_date AS DATE) AS purchase_day,
    SUM(total_price) AS daily_revenue
FROM User_Purchase
GROUP BY CAST(purchase_date AS DATE)
ORDER BY purchase_day;

---

Assignments

These are suitable for practice, interviews, or classroom exercises.

---

Assignment 1: Basic Data Retrieval

Task: Retrieve all users along with their city.

---

Assignment 2: Product Catalog View

Task: List all products showing productid, title, and price. Sort the results by price in descending order.

---

Assignment 3: Purchase History for a Specific User

Task: Display all purchases made by the user named "Arjun", including:

• product name

• quantity

• total price

• purchase date

---

Assignment 4: Total Revenue Generated

Task: Calculate the total revenue generated from all purchases.

---

Assignment 5: Total Spending Per User

Task: For each user, show:

• username

• city

• total amount spent

---

Assignment 6: Best-Selling Product

Task: Identify the product with the highest total quantity sold.

---

Assignment 7: Users with Multiple-Item Purchases

Task: Find users who have purchased more than one quantity in any single purchase.

---

Assignment 8: Products Never Purchased

Task: List all products that have never been purchased by any user.

---

Assignment 9: Daily Purchase Summary

Task: For each purchase date, show:

• number of purchases

• total revenue for that day

---

Assignment 10: Top Spending User

Task: Find the user who has spent the maximum total amount across all purchases.

---

Below are 10 additional SELECT-only SQL assignments, slightly more analytical and realistic, still fully based on your existing schema.

---

Assignment 11: Users Without Any Purchases

Task: List all users who have never made a purchase.

---

Assignment 12: Average Order Value

Task: Calculate the average total_price per purchase.

---

Assignment 13: City-Wise Revenue

Task: Show total revenue generated from each city.

---

Assignment 14: Most Recent Purchase Per User

Task: For each user, display their latest purchase date.

---

Assignment 15: Products with Revenue Above Average

Task: List products whose total revenue is greater than the average product revenue.

---

Assignment 16: Users Who Purchased the Same Product More Than Once

Task: Identify users who have purchased the same product multiple times.

---

Assignment 17: Purchase Count Per Product

Task: Show how many times each product was purchased (number of transactions).

---

Assignment 18: High-Value Purchases

Task: List all purchases where total_price is greater than ₹10,000 (or any threshold you choose).

---

Assignment 19: First Purchase Date of Each User

Task: For each user, find their first purchase date.

---

Assignment 20: Users Who Bought More Than One Product Type

Task: Find users who have purchased more than one distinct product.

---

Assignment 21: Highest Single Purchase Value

Task: Find the maximum total_price recorded in a single purchase.

---

Assignment 22: User Contribution Percentage

Task: For each user, calculate their percentage contribution to the overall revenue.

---

Assignment 23: Product Popularity Rank

Task: Rank products based on total quantity sold (highest first).

---

Assignment 24: Purchases Made in the Last 7 Days

Task: List all purchases made in the last 7 days from today.

---

Assignment 25: Average Quantity Per Product

Task: For each product, calculate the average quantity purchased per transaction.

---

Assignment 26: Users with Above-Average Spending

Task: List users whose total spending is above the average user spending.

---

Assignment 27: Most Purchased Product Per User

Task: For each user, identify the product they purchased the most (by quantity).

---

Assignment 28: Revenue Gap Between Top 2 Products

Task: Calculate the difference in total revenue between the highest-revenue product and the second highest.

---

Assignment 29: Purchase Frequency Per User

Task: For each user, show:

• total number of purchases

• first purchase date

• last purchase date

---

Assignment 30: Products Purchased by Users from Multiple Cities

Task: Find products that have been purchased by users from more than one city.

---

Assignment 31: Running Total Revenue Over Time

Task: Show purchase date, purchase id, and cumulative revenue ordered by purchase date.

---

Assignment 32: Revenue Share per Product (%)

Task: For each product, calculate its percentage share of total revenue.

---

Assignment 33: Users with Identical Total Spending

Task: Find users who have exactly the same total spending as at least one other user.

---

Assignment 34: Most Active Purchase Day

Task: Identify the day with the highest number of purchases.

---

Assignment 35: Products with Declining Sales

Task: Detect products where quantity sold decreased compared to the previous purchase (chronologically).

---

Assignment 36: Median Purchase Value

Task: Calculate the median total_price across all purchases.

---

Assignment 37: First-Time Buyers

Task: List purchases that represent a user’s first-ever purchase.

---

Assignment 38: User–Product Purchase Matrix

Task: Create a result set showing:

• username

• product title

• total quantity purchased by that user for that product

---

Assignment 39: Products Purchased on Consecutive Days

Task: Identify products that were purchased on two or more consecutive days.

---

Assignment 40: Top Product Per City

Task: For each city, find the highest revenue-generating product.

---

Below are 10 more SELECT-only SQL assignments (Assignments 41–50). These are expert-level, emphasizing analytical depth, edge cases, and advanced SQL reasoning in Microsoft SQL Server.

---

Assignment 41: Revenue Growth Between Purchases

Task: For each purchase, calculate the difference in total_price compared to the previous purchase (ordered by date).

---

Assignment 42: Longest Gap Between Purchases per User

Task: For each user, find the maximum number of days between any two consecutive purchases.

---

Assignment 43: Products Purchased by Every User

Task: Identify products that have been purchased by all users in the system.

---

Assignment 44: User Purchase Streak

Task: For each user, calculate the longest consecutive-day purchase streak.

---

Assignment 45: Revenue Percentile Ranking

Task: Rank purchases into percentiles based on total_price.

---

Assignment 46: City Revenue Ranking

Task: Rank cities based on total revenue generated.

---

Assignment 47: Products with Consistent Pricing

Task: List products whose unit price never changed across all purchases (assume price at purchase time = total_price / quantity).

---

Assignment 48: Users Whose Average Purchase Value Increased Over Time

Task: Identify users whose average purchase value shows an increasing trend over time.

---

Assignment 49: Purchase Distribution Buckets

Task: Group purchases into buckets:

• Low (< ₹5,000)

• Medium (₹5,000–₹20,000)

• High (> ₹20,000) and show count per bucket.

---

Assignment 50: Revenue Contribution Stability

Task: For each user, calculate the standard deviation of their purchase values and identify users with most stable spending behavior.

---

Tind: f37327ac-ff45-407e-8de2-b200851f369e