SQL

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);

Add 5 More Users

INSERT INTO [User] (username, city)
VALUES
('Suresh', 'Hyderabad'),
('Ananya', 'Delhi'),
('Vikram', 'Pune'),
('Neha', 'Jaipur'),
('Rohit', 'Kolkata');

---

Add 5 More Products

INSERT INTO Product (title, price)
VALUES
('External SSD 1TB', 9500.0),
('Bluetooth Headphones', 6500.0),
('Webcam HD', 3200.0),
('Laptop Stand', 2800.0),
('Gaming Chair', 22000.0);

---

Add 20 More Transactions

Assuming:

• Existing users: userid = 1–3

• New users: userid = 4–8

• Existing products: productid = 1–5

• New products: productid = 6–10

INSERT INTO User_Purchase
(product_id, user_id, purchase_date, quantity, total_price)
VALUES
-- User 4 (Suresh)
(1, 4, '2025-01-15', 1, 75000.0),
(6, 4, '2025-01-16', 1, 9500.0),
(2, 4, '2025-01-17', 2, 2400.0),

-- User 5 (Ananya)
(3, 5, '2025-01-15', 1, 4500.0),
(7, 5, '2025-01-16', 1, 6500.0),
(4, 5, '2025-01-18', 1, 18000.0),
(10, 5, '2025-01-19', 1, 22000.0),

-- User 6 (Vikram)
(5, 6, '2025-01-15', 2, 5000.0),
(8, 6, '2025-01-16', 1, 3200.0),
(9, 6, '2025-01-17', 1, 2800.0),
(6, 6, '2025-01-18', 1, 9500.0),

-- User 7 (Neha)
(2, 7, '2025-01-16', 1, 1200.0),
(3, 7, '2025-01-17', 2, 9000.0),
(7, 7, '2025-01-18', 1, 6500.0),
(4, 7, '2025-01-19', 1, 18000.0),

-- User 8 (Rohit)
(1, 8, '2025-01-16', 1, 75000.0),
(10, 8, '2025-01-17', 1, 22000.0),
(6, 8, '2025-01-18', 2, 19000.0),
(9, 8, '2025-01-19', 1, 2800.0);

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;

Results

24 rows returned

username	title	quantity	total_price	purchase_date
Arjun	Laptop	1	75000.0	2025-01-10T00:00:00
Arjun	Wireless Mouse	2	2400.0	2025-01-11T00:00:00
Meera	Mechanical Keyboard	1	4500.0	2025-01-12T00:00:00
Rahul	27-inch Monitor	1	18000.0	2025-01-13T00:00:00
Meera	USB-C Hub	3	7500.0	2025-01-14T00:00:00
Suresh	Laptop	1	75000.0	2025-01-15T00:00:00
Suresh	External SSD 1TB	1	9500.0	2025-01-16T00:00:00
Suresh	Wireless Mouse	2	2400.0	2025-01-17T00:00:00
Ananya	Mechanical Keyboard	1	4500.0	2025-01-15T00:00:00
Ananya	Bluetooth Headphones	1	6500.0	2025-01-16T00:00:00
Ananya	27-inch Monitor	1	18000.0	2025-01-18T00:00:00
Ananya	Gaming Chair	1	22000.0	2025-01-19T00:00:00
Vikram	USB-C Hub	2	5000.0	2025-01-15T00:00:00
Vikram	Webcam HD	1	3200.0	2025-01-16T00:00:00
Vikram	Laptop Stand	1	2800.0	2025-01-17T00:00:00
Vikram	External SSD 1TB	1	9500.0	2025-01-18T00:00:00
Neha	Wireless Mouse	1	1200.0	2025-01-16T00:00:00
Neha	Mechanical Keyboard	2	9000.0	2025-01-17T00:00:00
Neha	Bluetooth Headphones	1	6500.0	2025-01-18T00:00:00
Neha	27-inch Monitor	1	18000.0	2025-01-19T00:00:00
Rohit	Laptop	1	75000.0	2025-01-16T00:00:00
Rohit	Gaming Chair	1	22000.0	2025-01-17T00:00:00
Rohit	External SSD 1TB	2	19000.0	2025-01-18T00:00:00
Rohit	Laptop Stand	1	2800.0	2025-01-19T00:00:00

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;

Results

24 rows returned

purchaseid	username	city	product_name	quantity	unit_price	total_price	purchase_date
12	Ananya	Delhi	Gaming Chair	1	22000.0	22000.0	2025-01-19T00:00:00
20	Neha	Jaipur	27-inch Monitor	1	18000.0	18000.0	2025-01-19T00:00:00
24	Rohit	Kolkata	Laptop Stand	1	2800.0	2800.0	2025-01-19T00:00:00
23	Rohit	Kolkata	External SSD 1TB	2	9500.0	19000.0	2025-01-18T00:00:00
19	Neha	Jaipur	Bluetooth Headphones	1	6500.0	6500.0	2025-01-18T00:00:00
11	Ananya	Delhi	27-inch Monitor	1	18000.0	18000.0	2025-01-18T00:00:00
16	Vikram	Pune	External SSD 1TB	1	9500.0	9500.0	2025-01-18T00:00:00
8	Suresh	Hyderabad	Wireless Mouse	2	1200.0	2400.0	2025-01-17T00:00:00
22	Rohit	Kolkata	Gaming Chair	1	22000.0	22000.0	2025-01-17T00:00:00
15	Vikram	Pune	Laptop Stand	1	2800.0	2800.0	2025-01-17T00:00:00
18	Neha	Jaipur	Mechanical Keyboard	2	4500.0	9000.0	2025-01-17T00:00:00
21	Rohit	Kolkata	Laptop	1	75000.0	75000.0	2025-01-16T00:00:00
17	Neha	Jaipur	Wireless Mouse	1	1200.0	1200.0	2025-01-16T00:00:00
14	Vikram	Pune	Webcam HD	1	3200.0	3200.0	2025-01-16T00:00:00
7	Suresh	Hyderabad	External SSD 1TB	1	9500.0	9500.0	2025-01-16T00:00:00
10	Ananya	Delhi	Bluetooth Headphones	1	6500.0	6500.0	2025-01-16T00:00:00
13	Vikram	Pune	USB-C Hub	2	2500.0	5000.0	2025-01-15T00:00:00
9	Ananya	Delhi	Mechanical Keyboard	1	4500.0	4500.0	2025-01-15T00:00:00
6	Suresh	Hyderabad	Laptop	1	75000.0	75000.0	2025-01-15T00:00:00
5	Meera	Bangalore	USB-C Hub	3	2500.0	7500.0	2025-01-14T00:00:00
4	Rahul	Mumbai	27-inch Monitor	1	18000.0	18000.0	2025-01-13T00:00:00
3	Meera	Bangalore	Mechanical Keyboard	1	4500.0	4500.0	2025-01-12T00:00:00
2	Arjun	Chennai	Wireless Mouse	2	1200.0	2400.0	2025-01-11T00:00:00
1	Arjun	Chennai	Laptop	1	75000.0	75000.0	2025-01-10T00:00:00

---

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;

Results

8 rows returned

userid	username	city	total_spent
8	Rohit	Kolkata	118800.0
4	Suresh	Hyderabad	86900.0
1	Arjun	Chennai	77400.0
5	Ananya	Delhi	51000.0
7	Neha	Jaipur	34700.0
6	Vikram	Pune	20500.0
3	Rahul	Mumbai	18000.0
2	Meera	Bangalore	12000.0

---

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;

Results

10 rows returned

productid	title	total_units_sold	total_revenue
1	Laptop	3	225000.0
4	27-inch Monitor	3	54000.0
10	Gaming Chair	2	44000.0
6	External SSD 1TB	4	38000.0
3	Mechanical Keyboard	4	18000.0
7	Bluetooth Headphones	2	13000.0
5	USB-C Hub	5	12500.0
2	Wireless Mouse	5	6000.0
9	Laptop Stand	2	5600.0
8	Webcam HD	1	3200.0

---

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;

Results

6 rows returned

username	title	quantity	total_price
Arjun	Wireless Mouse	2	2400.0
Meera	USB-C Hub	3	7500.0
Suresh	Wireless Mouse	2	2400.0
Vikram	USB-C Hub	2	5000.0
Neha	Mechanical Keyboard	2	9000.0
Rohit	External SSD 1TB	2	19000.0

---

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;

Results 10 rows returned

purchase_day	daily_revenue
2025-01-10	75000.0
2025-01-11	2400.0
2025-01-12	4500.0
2025-01-13	18000.0
2025-01-14	7500.0
2025-01-15	84500.0
2025-01-16	95400.0
2025-01-17	36200.0
2025-01-18	53000.0
2025-01-19	42800.0

---

Assignments

Below are 10 well-structured SQL assignments focused only on SELECT statements, designed to progressively improve querying, filtering, aggregation, and analytical thinking using your existing schema.

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

---

Assignment 1: Basic Data Retrieval

Task: Retrieve all users along with their city.

SELECT
    u.username,
    u.city
FROM [User] u;

Results

8 rows returned

username	city
Arjun	Chennai
Meera	Bangalore
Rahul	Mumbai
Suresh	Hyderabad
Ananya	Delhi
Vikram	Pune
Neha	Jaipur
Rohit	Kolkata

---

Assignment 2: Product Catalog View

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

SELECT
    p.productid,
    p.title,
    p.price
FROM [Product] as p

10 rows returned

productid	title	price
1	Laptop	75000.0
2	Wireless Mouse	1200.0
3	Mechanical Keyboard	4500.0
4	27-inch Monitor	18000.0
5	USB-C Hub	2500.0
6	External SSD 1TB	9500.0
7	Bluetooth Headphones	6500.0
8	Webcam HD	3200.0
9	Laptop Stand	2800.0
10	Gaming Chair	22000.0

---

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

SELECT
    p.title AS product_name,
    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
WHERE u.username = 'Arjun';

2 rows returned

product_name	quantity	total_price	purchase_date
Laptop	1	75000.0	2025-01-10T00:00:00
Wireless Mouse	2	2400.0	2025-01-11T00:00:00

---

Assignment 4: Total Revenue Generated

Task: Calculate the total revenue generated from all purchases.

SELECT
    SUM(total_price) AS total_revenue
FROM User_Purchase;

Results

1 rows returned

total_revenue
419300.0

---

Assignment 5: Total Spending Per User

Task: For each user, show:

• username

• city

• total amount spent

Results
8 rows returned
username	city	total_amount_spent
Rohit	Kolkata	118800.0
Suresh	Hyderabad	86900.0
Arjun	Chennai	77400.0
Ananya	Delhi	51000.0
Neha	Jaipur	34700.0
Vikram	Pune	20500.0
Rahul	Mumbai	18000.0
Meera	Bangalore	12000.0

Results

8 rows returned

username	city	total_amount_spent
Rohit	Kolkata	118800.0
Suresh	Hyderabad	86900.0
Arjun	Chennai	77400.0
Ananya	Delhi	51000.0
Neha	Jaipur	34700.0
Vikram	Pune	20500.0
Rahul	Mumbai	18000.0
Meera	Bangalore	12000.0

---

Assignment 6: Best-Selling Product

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

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

Results

1 rows returned

productid	title	total_quantity_sold
2	Wireless Mouse	5

---

Assignment 7: Users with Multiple-Item Purchases

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

SELECT DISTINCT
    u.username,
    u.city
FROM User_Purchase up
JOIN [User] u
    ON up.user_id = u.userid
WHERE up.quantity > 1;

Results

X rows returned

username	city
Arjun	Chennai
Meera	Bangalore
Neha	Jaipur
Suresh	Hyderabad
(Result depends on data)

---

Assignment 8: Products Never Purchased

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

SELECT
    p.productid,
    p.title,
    p.price
FROM Product p
LEFT JOIN User_Purchase up
    ON p.productid = up.product_id
WHERE up.product_id IS NULL;

Results

X rows returned

productid	title	price
…	…	…

---

Assignment 9: Daily Purchase Summary

Task: For each purchase date, show:

• number of purchases

• total revenue for that day

SELECT
    CAST(purchase_date AS DATE) AS purchase_date,
    COUNT(purchaseid) AS number_of_purchases,
    SUM(total_price) AS total_revenue
FROM User_Purchase
GROUP BY CAST(purchase_date AS DATE)
ORDER BY purchase_date;

Results

X rows returned

purchase_date	number_of_purchases	total_revenue
2025-01-10	1	75000.0
2025-01-11	1	2400.0
…	…	…

---

Assignment 10: Top Spending User

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

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

Results

1 row returned

username	city	total_amount_spent
Rohit	Kolkata	118800.0

---

Assignment 11: Users Without Any Purchases

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

SELECT
    u.username,
    u.city
FROM [User] u
LEFT JOIN User_Purchase up
    ON u.userid = up.user_id
WHERE up.user_id IS NULL;

Results

0 rows returned

username	city

---

Assignment 12: Average Order Value

Task: Calculate the average total_price per purchase.

SELECT
    AVG(total_price) AS average_order_value
FROM User_Purchase;

Results

1 rows returned

average_order_value
17470.833333333332

---

Assignment 13: City-Wise Revenue

Task: Show total revenue generated from each city.

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

Results

8 rows returned

city	total_revenue
Kolkata	118800
Hyderabad	86900
Chennai	77400
Delhi	51000
Jaipur	34700
Pune	20500
Mumbai	18000
Bangalore	12000

---

Assignment 14: Most Recent Purchase Per User

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

SELECT
    u.username,
    u.city,
    MAX(up.purchase_date) AS latest_purchase_date
FROM [User] u
JOIN User_Purchase up
    ON u.userid = up.user_id
GROUP BY u.username, u.city
ORDER BY u.username;

Results

8 rows returned

username	city	latest_purchase_date
Ananya	Delhi	2025-01-19 00:00:00
Arjun	Chennai	2025-01-11 00:00:00
Meera	Bangalore	2025-01-14 00:00:00
Neha	Jaipur	2025-01-19 00:00:00
Rahul	Mumbai	2025-01-13 00:00:00
Rohit	Kolkata	2025-01-19 00:00:00
Suresh	Hyderabad	2025-01-17 00:00:00
Vikram	Pune	2025-01-18 00:00:00

---

Assignment 15: Products with Revenue Above Average

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

WITH ProductRevenue AS (
    SELECT
        p.productid,
        p.title,
        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
),
AvgProductRevenue AS (
    SELECT AVG(total_revenue) AS avg_revenue
    FROM ProductRevenue
)
SELECT
    pr.productid,
    pr.title,
    pr.total_revenue
FROM ProductRevenue pr
CROSS JOIN AvgProductRevenue a
WHERE pr.total_revenue > a.avg_revenue
ORDER BY pr.total_revenue DESC;

Results

3 rows returned

productid	title	total_revenue
1	Laptop	225000
4	27-inch Monitor	54000
10	Gaming Chair	44000

---

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

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

SELECT
    u.username,
    p.title AS product_name,
    COUNT(*) AS purchase_count
FROM User_Purchase up
JOIN [User] u
    ON up.user_id = u.userid
JOIN Product p
    ON up.product_id = p.productid
GROUP BY u.username, p.title
HAVING COUNT(*) > 1
ORDER BY purchase_count DESC, u.username, product_name;

Results

0 rows returned

username	product	purchase_count

---

Assignment 17: Purchase Count Per Product

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

SELECT
    p.productid,
    p.title,
    COUNT(up.purchaseid) AS purchase_count
FROM Product p
JOIN User_Purchase up
    ON p.productid = up.product_id
GROUP BY p.productid, p.title
ORDER BY purchase_count DESC, p.productid;

Results

10 rows returned

productid	title	purchase_count
1	Laptop	3
2	Wireless Mouse	3
3	Mechanical Keyboard	3
4	27-inch Monitor	3
6	External SSD 1TB	3
5	USB-C Hub	2
7	Bluetooth Headphones	2
9	Laptop Stand	2
10	Gaming Chair	2
8	Webcam HD	1

---

Assignment 18: High-Value Purchases

Task: List all purchases where total_price is greater than ₹10,000.

SELECT
    up.purchaseid,
    u.username,
    p.title AS product_name,
    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
WHERE up.total_price > 10000
ORDER BY up.total_price DESC, up.purchase_date;

Results

9 rows returned

purchaseid	username	product_name	quantity	total_price	purchase_date
1	Arjun	Laptop	1	75000	2025-01-10
6	Suresh	Laptop	1	75000	2025-01-15
21	Rohit	Laptop	1	75000	2025-01-16
12	Ananya	Gaming Chair	1	22000	2025-01-19
22	Rohit	Gaming Chair	1	22000	2025-01-17
4	Rahul	27-inch Monitor	1	18000	2025-01-13
11	Ananya	27-inch Monitor	1	18000	2025-01-18
20	Neha	27-inch Monitor	1	18000	2025-01-19
23	Rohit	External SSD 1TB	2	19000	2025-01-18

---

Assignment 19: First Purchase Date of Each User

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

SELECT
    u.username,
    u.city,
    MIN(up.purchase_date) AS first_purchase_date
FROM [User] u
JOIN User_Purchase up
    ON u.userid = up.user_id
GROUP BY u.username, u.city
ORDER BY u.username;

Results

8 rows returned

username	city	first_purchase_date
Ananya	Delhi	2025-01-15 00:00:00
Arjun	Chennai	2025-01-10 00:00:00
Meera	Bangalore	2025-01-12 00:00:00
Neha	Jaipur	2025-01-16 00:00:00
Rahul	Mumbai	2025-01-13 00:00:00
Rohit	Kolkata	2025-01-16 00:00:00
Suresh	Hyderabad	2025-01-15 00:00:00
Vikram	Pune	2025-01-15 00:00:00

---

Assignment 20: Users Who Bought More Than One Product Type

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

SELECT
    u.username,
    u.city,
    COUNT(DISTINCT up.product_id) AS distinct_products
FROM [User] u
JOIN User_Purchase up
    ON u.userid = up.user_id
GROUP BY u.username, u.city
HAVING COUNT(DISTINCT up.product_id) > 1
ORDER BY distinct_products DESC, u.username;

Results

7 rows returned

username	city	distinct_products
Ananya	Delhi	4
Vikram	Pune	4
Neha	Jaipur	4
Rohit	Kolkata	4
Suresh	Hyderabad	3
Arjun	Chennai	2
Meera	Bangalore	2

---

Below are 10 more SELECT-only SQL assignments (Assignments 21–30). These focus on deeper analysis, edge cases, and interview-grade reasoning, while still staying strictly within your schema.

---

Assignment 21: Highest Single Purchase Value

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

SELECT
    MAX(total_price) AS highest_single_purchase_value
FROM User_Purchase;

Results

1 rows returned

highest_single_purchase_value
75000

---

Assignment 22: User Contribution Percentage

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

WITH UserTotals AS (
    SELECT
        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.username, u.city
),
Overall AS (
    SELECT SUM(total_spent) AS overall_revenue
    FROM UserTotals
)
SELECT
    ut.username,
    ut.city,
    ut.total_spent,
    ROUND(ut.total_spent * 100.0 / o.overall_revenue, 2) AS revenue_pct
FROM UserTotals ut
CROSS JOIN Overall o
ORDER BY revenue_pct DESC;

Results

8 rows returned

username	city	total_spent	revenue_pct
Rohit	Kolkata	118800	28.33
Suresh	Hyderabad	86900	20.73
Arjun	Chennai	77400	18.46
Ananya	Delhi	51000	12.16
Neha	Jaipur	34700	8.28
Vikram	Pune	20500	4.89
Rahul	Mumbai	18000	4.29
Meera	Bangalore	12000	2.86

---

Assignment 23: Product Popularity Rank

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

SELECT
    p.productid,
    p.title,
    SUM(up.quantity) AS total_quantity_sold,
    DENSE_RANK() OVER (ORDER BY SUM(up.quantity) DESC) AS popularity_rank
FROM Product p
JOIN User_Purchase up
    ON p.productid = up.product_id
GROUP BY p.productid, p.title
ORDER BY popularity_rank, p.productid;

Results

10 rows returned

productid	title	total_quantity_sold	popularity_rank
2	Wireless Mouse	5	1
5	USB-C Hub	5	1
3	Mechanical Keyboard	4	2
6	External SSD 1TB	4	2
1	Laptop	3	3
4	27-inch Monitor	3	3
7	Bluetooth Headphones	2	4
9	Laptop Stand	2	4
10	Gaming Chair	2	4
8	Webcam HD	1	5

---

Assignment 24: Purchases Made in the Last 7 Days

Task: List all purchases made in the last 7 days (relative to the latest purchase date in the table).

SELECT
    up.purchaseid,
    u.username,
    p.title AS product_name,
    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
WHERE up.purchase_date >= DATEADD(day, -7, (SELECT MAX(purchase_date) FROM User_Purchase))
ORDER BY up.purchase_date, up.purchaseid;

Results

22 rows returned

purchaseid	username	product_name	quantity	total_price	purchase_date
3	Meera	Mechanical Keyboard	1	4500	2025-01-12
4	Rahul	27-inch Monitor	1	18000	2025-01-13
5	Meera	USB-C Hub	3	7500	2025-01-14
6	Suresh	Laptop	1	75000	2025-01-15
9	Ananya	Mechanical Keyboard	1	4500	2025-01-15
13	Vikram	USB-C Hub	2	5000	2025-01-15
7	Suresh	External SSD 1TB	1	9500	2025-01-16
10	Ananya	Bluetooth Headphones	1	6500	2025-01-16
14	Vikram	Webcam HD	1	3200	2025-01-16
17	Neha	Wireless Mouse	1	1200	2025-01-16
21	Rohit	Laptop	1	75000	2025-01-16
8	Suresh	Wireless Mouse	2	2400	2025-01-17
15	Vikram	Laptop Stand	1	2800	2025-01-17
18	Neha	Mechanical Keyboard	2	9000	2025-01-17
22	Rohit	Gaming Chair	1	22000	2025-01-17
11	Ananya	27-inch Monitor	1	18000	2025-01-18
16	Vikram	External SSD 1TB	1	9500	2025-01-18
19	Neha	Bluetooth Headphones	1	6500	2025-01-18
23	Rohit	External SSD 1TB	2	19000	2025-01-18
12	Ananya	Gaming Chair	1	22000	2025-01-19
20	Neha	27-inch Monitor	1	18000	2025-01-19
24	Rohit	Laptop Stand	1	2800	2025-01-19

---

Assignment 25: Average Quantity Per Product

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

SELECT
    p.productid,
    p.title,
    ROUND(AVG(CAST(up.quantity AS FLOAT)), 2) AS avg_quantity_per_txn
FROM Product p
JOIN User_Purchase up
    ON p.productid = up.product_id
GROUP BY p.productid, p.title
ORDER BY avg_quantity_per_txn DESC, p.productid;

Results

10 rows returned

productid	title	avg_quantity_per_txn
5	USB-C Hub	2.50
2	Wireless Mouse	1.67
3	Mechanical Keyboard	1.33
6	External SSD 1TB	1.33
1	Laptop	1.00
4	27-inch Monitor	1.00
7	Bluetooth Headphones	1.00
8	Webcam HD	1.00
9	Laptop Stand	1.00
10	Gaming Chair	1.00

---

Assignment 26: Users with Above-Average Spending

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

WITH UserTotals AS (
    SELECT
        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.username, u.city
),
AvgSpend AS (
    SELECT AVG(total_spent) AS avg_user_spend
    FROM UserTotals
)
SELECT
    ut.username,
    ut.city,
    ut.total_spent
FROM UserTotals ut
CROSS JOIN AvgSpend a
WHERE ut.total_spent > a.avg_user_spend
ORDER BY ut.total_spent DESC;

Results

3 rows returned

username	city	total_spent
Rohit	Kolkata	118800
Suresh	Hyderabad	86900
Arjun	Chennai	77400

---

Assignment 27: Most Purchased Product Per User

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

WITH UserProductTotals AS (
    SELECT
        u.username,
        u.city,
        p.title AS product_name,
        SUM(up.quantity) AS total_quantity
    FROM User_Purchase up
    JOIN [User] u
        ON up.user_id = u.userid
    JOIN Product p
        ON up.product_id = p.productid
    GROUP BY u.username, u.city, p.title
),
Ranked AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY username ORDER BY total_quantity DESC) AS rnk
    FROM UserProductTotals
)
SELECT
    username,
    city,
    product_name AS most_purchased_product,
    total_quantity
FROM Ranked
WHERE rnk = 1
ORDER BY username, most_purchased_product;

Results

11 rows returned

username	city	most_purchased_product	total_quantity
Ananya	Delhi	27-inch Monitor	1
Ananya	Delhi	Bluetooth Headphones	1
Ananya	Delhi	Gaming Chair	1
Ananya	Delhi	Mechanical Keyboard	1
Arjun	Chennai	Wireless Mouse	2
Meera	Bangalore	USB-C Hub	3
Neha	Jaipur	Mechanical Keyboard	2
Rahul	Mumbai	27-inch Monitor	1
Rohit	Kolkata	External SSD 1TB	2
Suresh	Hyderabad	Wireless Mouse	2
Vikram	Pune	USB-C Hub	2

---

Assignment 28: Revenue Gap Between Top 2 Products

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

WITH ProductRevenue AS (
    SELECT
        p.title,
        SUM(up.total_price) AS total_revenue
    FROM Product p
    JOIN User_Purchase up
        ON p.productid = up.product_id
    GROUP BY p.title
),
Ranked AS (
    SELECT
        title,
        total_revenue,
        DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS rnk
    FROM ProductRevenue
)
SELECT
    MAX(CASE WHEN rnk = 1 THEN total_revenue END)
  - MAX(CASE WHEN rnk = 2 THEN total_revenue END) AS revenue_gap_top2_products
FROM Ranked;

Results

1 rows returned

revenue_gap_top2_products
171000

---

Assignment 29: Purchase Frequency Per User

Task: For each user, show total number of purchases, first purchase date, last purchase date.

SELECT
    u.username,
    u.city,
    COUNT(up.purchaseid) AS total_purchases,
    MIN(up.purchase_date) AS first_purchase_date,
    MAX(up.purchase_date) AS last_purchase_date
FROM [User] u
JOIN User_Purchase up
    ON u.userid = up.user_id
GROUP BY u.username, u.city
ORDER BY total_purchases DESC, u.username;

Results

8 rows returned

username	city	total_purchases	first_purchase_date	last_purchase_date
Ananya	Delhi	4	2025-01-15 00:00:00	2025-01-19 00:00:00
Vikram	Pune	4	2025-01-15 00:00:00	2025-01-18 00:00:00
Neha	Jaipur	4	2025-01-16 00:00:00	2025-01-19 00:00:00
Rohit	Kolkata	4	2025-01-16 00:00:00	2025-01-19 00:00:00
Suresh	Hyderabad	3	2025-01-15 00:00:00	2025-01-17 00:00:00
Arjun	Chennai	2	2025-01-10 00:00:00	2025-01-11 00:00:00
Meera	Bangalore	2	2025-01-12 00:00:00	2025-01-14 00:00:00
Rahul	Mumbai	1	2025-01-13 00:00:00	2025-01-13 00:00:00

---

Assignment 30: Products Purchased by Users from Multiple Cities

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

SELECT
    p.productid,
    p.title,
    COUNT(DISTINCT u.city) AS cities_count
FROM User_Purchase up
JOIN [User] u
    ON up.user_id = u.userid
JOIN Product p
    ON up.product_id = p.productid
GROUP BY p.productid, p.title
HAVING COUNT(DISTINCT u.city) > 1
ORDER BY cities_count DESC, p.productid;

Results

9 rows returned

productid	title	cities_count
1	Laptop	3
2	Wireless Mouse	3
3	Mechanical Keyboard	3
4	27-inch Monitor	3
6	External SSD 1TB	3
5	USB-C Hub	2
7	Bluetooth Headphones	2
9	Laptop Stand	2
10	Gaming Chair	2

---

Below are 10 additional SELECT-only SQL assignments (Assignments 31–40). These are more advanced and closer to real-world analytics and interview scenarios, while remaining strictly within your schema.

---

Assignment 31: Running Total Revenue Over Time

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

SELECT
    purchase_date,
    purchaseid,
    SUM(total_price) OVER (ORDER BY purchase_date, purchaseid) AS cumulative_revenue
FROM User_Purchase
ORDER BY purchase_date, purchaseid;

Results

24 rows returned

purchase_date	purchaseid	cumulative_revenue
2025-01-10	1	75000
2025-01-11	2	77400
2025-01-12	3	81900
2025-01-13	4	99900
2025-01-14	5	107400
2025-01-15	6	182400
2025-01-15	9	186900
2025-01-15	13	191900
2025-01-16	7	201400
2025-01-16	10	207900
2025-01-16	14	211100
2025-01-16	17	212300
2025-01-16	21	287300
2025-01-17	8	289700
2025-01-17	15	292500
2025-01-17	18	301500
2025-01-17	22	323500
2025-01-18	11	341500
2025-01-18	16	351000
2025-01-18	19	357500
2025-01-18	23	376500
2025-01-19	12	398500
2025-01-19	20	416500
2025-01-19	24	419300

---

Assignment 32: Revenue Share per Product (%)

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

WITH ProductRevenue AS (
    SELECT
        p.productid,
        p.title,
        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
),
Overall AS (
    SELECT SUM(total_revenue) AS overall_revenue
    FROM ProductRevenue
)
SELECT
    pr.productid,
    pr.title,
    pr.total_revenue,
    ROUND(pr.total_revenue * 100.0 / o.overall_revenue, 2) AS revenue_share_pct
FROM ProductRevenue pr
CROSS JOIN Overall o
ORDER BY revenue_share_pct DESC;

Results

10 rows returned

productid	title	total_revenue	revenue_share_pct
1	Laptop	225000	53.66
4	27-inch Monitor	54000	12.88
10	Gaming Chair	44000	10.49
6	External SSD 1TB	38000	9.06
3	Mechanical Keyboard	18000	4.29
7	Bluetooth Headphones	13000	3.10
5	USB-C Hub	12500	2.98
2	Wireless Mouse	6000	1.43
9	Laptop Stand	5600	1.34
8	Webcam HD	3200	0.76

---

Assignment 33: Users with Identical Total Spending

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

WITH UserTotals AS (
    SELECT
        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.username, u.city
)
SELECT
    ut.username,
    ut.city,
    ut.total_spent
FROM UserTotals ut
WHERE ut.total_spent IN (
    SELECT total_spent
    FROM UserTotals
    GROUP BY total_spent
    HAVING COUNT(*) > 1
)
ORDER BY ut.total_spent, ut.username;

Results

0 rows returned

username	city	total_spent

---

Assignment 34: Most Active Purchase Day

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

SELECT TOP 1
    CAST(purchase_date AS DATE) AS purchase_day,
    COUNT(*) AS number_of_purchases
FROM User_Purchase
GROUP BY CAST(purchase_date AS DATE)
ORDER BY number_of_purchases DESC, purchase_day;

Results

1 rows returned

purchase_date	number_of_purchases
2025-01-16	5

---

Assignment 35: Products with Declining Sales

Task: Detect product purchases where quantity decreased compared to the previous purchase (chronologically, per product).

WITH ProductPurchases AS (
    SELECT
        up.purchaseid,
        up.product_id,
        p.title,
        CAST(up.purchase_date AS DATE) AS purchase_day,
        up.quantity,
        LAG(up.quantity) OVER (PARTITION BY up.product_id ORDER BY up.purchase_date, up.purchaseid) AS previous_quantity
    FROM User_Purchase up
    JOIN Product p
        ON up.product_id = p.productid
)
SELECT
    purchaseid,
    product_id AS productid,
    title,
    purchase_day AS purchase_date,
    previous_quantity,
    quantity AS current_quantity
FROM ProductPurchases
WHERE previous_quantity IS NOT NULL
  AND quantity < previous_quantity
ORDER BY productid, purchase_date, purchaseid;

Results

2 rows returned

purchaseid	productid	title	purchase_date	previous_quantity	current_quantity
17	2	Wireless Mouse	2025-01-16	2	1
13	5	USB-C Hub	2025-01-15	3	2

---

Assignment 36: Median Purchase Value

Task: Calculate the median total_price across all purchases.

SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_price) OVER () AS median_purchase_value
FROM User_Purchase
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

Results

1 rows returned

median_purchase_value
8250

---

Assignment 37: First-Time Buyers

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

WITH FirstPurchase AS (
    SELECT
        user_id,
        MIN(purchase_date) AS first_purchase_date
    FROM User_Purchase
    GROUP BY user_id
)
SELECT
    up.purchaseid,
    u.username,
    p.title AS product_name,
    up.quantity,
    up.total_price,
    up.purchase_date
FROM User_Purchase up
JOIN FirstPurchase fp
    ON up.user_id = fp.user_id
   AND up.purchase_date = fp.first_purchase_date
JOIN [User] u
    ON up.user_id = u.userid
JOIN Product p
    ON up.product_id = p.productid
ORDER BY u.username, up.purchaseid;

Results

8 rows returned

purchaseid	username	product_name	quantity	total_price	purchase_date
9	Ananya	Mechanical Keyboard	1	4500	2025-01-15
1	Arjun	Laptop	1	75000	2025-01-10
3	Meera	Mechanical Keyboard	1	4500	2025-01-12
17	Neha	Wireless Mouse	1	1200	2025-01-16
4	Rahul	27-inch Monitor	1	18000	2025-01-13
21	Rohit	Laptop	1	75000	2025-01-16
6	Suresh	Laptop	1	75000	2025-01-15
13	Vikram	USB-C Hub	2	5000	2025-01-15

---

Assignment 38: User–Product Purchase Matrix

Task: Show username, product title, and total quantity purchased by that user for that product.

SELECT
    u.username,
    p.title AS product_name,
    SUM(up.quantity) AS total_quantity
FROM User_Purchase up
JOIN [User] u
    ON up.user_id = u.userid
JOIN Product p
    ON up.product_id = p.productid
GROUP BY u.username, p.title
ORDER BY u.username, product_name;

Results

24 rows returned

username	product_name	total_quantity
Ananya	27-inch Monitor	1
Ananya	Bluetooth Headphones	1
Ananya	Gaming Chair	1
Ananya	Mechanical Keyboard	1
Arjun	Laptop	1
Arjun	Wireless Mouse	2
Meera	Mechanical Keyboard	1
Meera	USB-C Hub	3
Neha	27-inch Monitor	1
Neha	Bluetooth Headphones	1
Neha	Mechanical Keyboard	2
Neha	Wireless Mouse	1
Rahul	27-inch Monitor	1
Rohit	External SSD 1TB	2
Rohit	Gaming Chair	1
Rohit	Laptop	1
Rohit	Laptop Stand	1
Suresh	External SSD 1TB	1
Suresh	Laptop	1
Suresh	Wireless Mouse	2
Vikram	External SSD 1TB	1
Vikram	Laptop Stand	1
Vikram	USB-C Hub	2
Vikram	Webcam HD	1

---

Assignment 39: Products Purchased on Consecutive Days

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

WITH ProductDays AS (
    SELECT DISTINCT
        product_id,
        CAST(purchase_date AS DATE) AS purchase_day
    FROM User_Purchase
),
Consecutive AS (
    SELECT
        product_id,
        purchase_day,
        LAG(purchase_day) OVER (PARTITION BY product_id ORDER BY purchase_day) AS prev_day
    FROM ProductDays
)
SELECT DISTINCT
    p.productid,
    p.title
FROM Consecutive c
JOIN Product p
    ON c.product_id = p.productid
WHERE DATEDIFF(day, c.prev_day, c.purchase_day) = 1
ORDER BY p.productid;

Results

4 rows returned

productid	title
1	Laptop
2	Wireless Mouse
4	27-inch Monitor
5	USB-C Hub

---

Assignment 40: Top Product Per City

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

WITH CityProductRevenue AS (
    SELECT
        u.city,
        p.productid,
        p.title,
        SUM(up.total_price) AS total_revenue
    FROM User_Purchase up
    JOIN [User] u
        ON up.user_id = u.userid
    JOIN Product p
        ON up.product_id = p.productid
    GROUP BY u.city, p.productid, p.title
),
Ranked AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY city ORDER BY total_revenue DESC) AS rnk
    FROM CityProductRevenue
)
SELECT
    city,
    productid,
    title AS top_product,
    total_revenue AS top_product_revenue
FROM Ranked
WHERE rnk = 1
ORDER BY city;

Results

8 rows returned

city	productid	top_product	top_product_revenue
Bangalore	5	USB-C Hub	7500
Chennai	1	Laptop	75000
Delhi	10	Gaming Chair	22000
Hyderabad	1	Laptop	75000
Jaipur	4	27-inch Monitor	18000
Kolkata	1	Laptop	75000
Mumbai	4	27-inch Monitor	18000
Pune	6	External SSD 1TB	9500

---

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 difference in total_price compared to previous purchase (ordered by date).

SELECT
    CAST(purchase_date AS DATE) AS purchase_date,
    purchaseid,
    total_price,
    LAG(total_price) OVER (ORDER BY purchase_date, purchaseid) AS prev_total_price,
    total_price - LAG(total_price) OVER (ORDER BY purchase_date, purchaseid) AS revenue_diff
FROM User_Purchase
ORDER BY purchase_date, purchaseid;

Results

24 rows returned

purchase_date	purchaseid	total_price	prev_total_price	revenue_diff
2025-01-10	1	75000	NaN	NaN
2025-01-11	2	2400	75000	-72600
2025-01-12	3	4500	2400	2100
2025-01-13	4	18000	4500	13500
2025-01-14	5	7500	18000	-10500
2025-01-15	6	75000	7500	67500
2025-01-15	9	4500	75000	-70500
2025-01-15	13	5000	4500	500
2025-01-16	7	9500	5000	4500
2025-01-16	10	6500	9500	-3000
2025-01-16	14	3200	6500	-3300
2025-01-16	17	1200	3200	-2000
2025-01-16	21	75000	1200	73800
2025-01-17	8	2400	75000	-72600
2025-01-17	15	2800	2400	400
2025-01-17	18	9000	2800	6200
2025-01-17	22	22000	9000	13000
2025-01-18	11	18000	22000	-4000
2025-01-18	16	9500	18000	-8500
2025-01-18	19	6500	9500	-3000
2025-01-18	23	19000	6500	12500
2025-01-19	12	22000	19000	3000
2025-01-19	20	18000	22000	-4000
2025-01-19	24	2800	18000	-15200

---

Assignment 42: Longest Gap Between Purchases per User

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

WITH UserDays AS (
    SELECT DISTINCT
        user_id,
        CAST(purchase_date AS DATE) AS purchase_day
    FROM User_Purchase
),
Gaps AS (
    SELECT
        user_id,
        purchase_day,
        LAG(purchase_day) OVER (PARTITION BY user_id ORDER BY purchase_day) AS prev_day
    FROM UserDays
)
SELECT
    u.username,
    u.city,
    ISNULL(MAX(DATEDIFF(day, prev_day, purchase_day)), 0) AS max_gap_days
FROM [User] u
LEFT JOIN Gaps g
    ON u.userid = g.user_id
GROUP BY u.username, u.city
ORDER BY max_gap_days DESC, u.username;

Results

8 rows returned

username	city	max_gap_days
Ananya	Delhi	2
Meera	Bangalore	2
Arjun	Chennai	1
Neha	Jaipur	1
Rohit	Kolkata	1
Suresh	Hyderabad	1
Vikram	Pune	1
Rahul	Mumbai	0

---

Assignment 43: Products Purchased by Every User

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

SELECT
    p.productid,
    p.title
FROM Product p
JOIN User_Purchase up
    ON p.productid = up.product_id
GROUP BY p.productid, p.title
HAVING COUNT(DISTINCT up.user_id) = (SELECT COUNT(*) FROM [User])
ORDER BY p.productid;

Results

0 rows returned

productid	title

---

Assignment 44: User Purchase Streak

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

WITH UserDays AS (
    SELECT DISTINCT
        user_id,
        CAST(purchase_date AS DATE) AS purchase_day
    FROM User_Purchase
),
Islands AS (
    SELECT
        user_id,
        purchase_day,
        DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_day), purchase_day) AS grp
    FROM UserDays
),
Streaks AS (
    SELECT
        user_id,
        COUNT(*) AS streak_length
    FROM Islands
    GROUP BY user_id, grp
)
SELECT
    u.username,
    u.city,
    MAX(s.streak_length) AS longest_streak_days
FROM [User] u
LEFT JOIN Streaks s
    ON u.userid = s.user_id
GROUP BY u.username, u.city
ORDER BY longest_streak_days DESC, u.username;

Results

8 rows returned

username	city	longest_streak_days
Neha	Jaipur	4
Rohit	Kolkata	4
Vikram	Pune	4
Suresh	Hyderabad	3
Ananya	Delhi	2
Arjun	Chennai	2
Meera	Bangalore	1
Rahul	Mumbai	1

---

Assignment 45: Revenue Percentile Ranking

Task: Rank purchases into percentiles based on total_price (PERCENT_RANK).

SELECT
    purchaseid,
    total_price,
    ROUND(PERCENT_RANK() OVER (ORDER BY total_price) * 100.0, 2) AS percent_rank
FROM User_Purchase
ORDER BY total_price DESC, purchaseid;

Results

24 rows returned

purchaseid	total_price	percent_rank
1	75000	91.30
6	75000	91.30
21	75000	91.30
12	22000	82.61
22	22000	82.61
23	19000	78.26
4	18000	65.22
11	18000	65.22
20	18000	65.22
7	9500	56.52
16	9500	56.52
5	7500	47.83
10	6500	39.13
19	6500	39.13
13	5000	30.43
3	4500	21.74
9	4500	21.74
18	9000	52.17
14	3200	13.04
15	2800	8.70
24	2800	8.70
2	2400	4.35
8	2400	4.35
17	1200	0.00

---

Assignment 46: City Revenue Ranking

Task: Rank cities based on total revenue generated.

WITH CityRevenue AS (
    SELECT
        u.city,
        SUM(up.total_price) AS total_revenue
    FROM [User] u
    JOIN User_Purchase up
        ON u.userid = up.user_id
    GROUP BY u.city
)
SELECT
    city,
    total_revenue,
    DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS city_rank
FROM CityRevenue
ORDER BY city_rank, city;

Results

8 rows returned

city	total_revenue	city_rank
Kolkata	118800	1
Hyderabad	86900	2
Chennai	77400	3
Delhi	51000	4
Jaipur	34700	5
Pune	20500	6
Mumbai	18000	7
Bangalore	12000	8

---

Assignment 47: Products with Consistent Pricing

Task: List products whose unit price never changed across all purchases.

WITH UnitPrices AS (
    SELECT
        product_id,
        CAST(total_price / NULLIF(quantity, 0) AS FLOAT) AS unit_price_at_purchase
    FROM User_Purchase
)
SELECT
    p.productid,
    p.title
FROM Product p
JOIN UnitPrices up
    ON p.productid = up.product_id
GROUP BY p.productid, p.title
HAVING COUNT(DISTINCT up.unit_price_at_purchase) = 1
ORDER BY p.productid;

Results

10 rows returned

productid	title
1	Laptop
2	Wireless Mouse
3	Mechanical Keyboard
4	27-inch Monitor
5	USB-C Hub
6	External SSD 1TB
7	Bluetooth Headphones
8	Webcam HD
9	Laptop Stand
10	Gaming Chair

---

Assignment 48: Users Whose Average Purchase Value Increased Over Time

Task: Identify users whose last purchase value is greater than their first purchase value.

WITH Ordered AS (
    SELECT
        up.user_id,
        up.purchase_date,
        up.total_price,
        ROW_NUMBER() OVER (PARTITION BY up.user_id ORDER BY up.purchase_date, up.purchaseid) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY up.user_id ORDER BY up.purchase_date DESC, up.purchaseid DESC) AS rn_desc
    FROM User_Purchase up
),
FirstLast AS (
    SELECT
        user_id,
        MAX(CASE WHEN rn_asc = 1 THEN total_price END) AS first_purchase_value,
        MAX(CASE WHEN rn_desc = 1 THEN total_price END) AS last_purchase_value
    FROM Ordered
    GROUP BY user_id
)
SELECT
    u.username,
    u.city
FROM FirstLast fl
JOIN [User] u
    ON fl.user_id = u.userid
WHERE fl.last_purchase_value > fl.first_purchase_value
ORDER BY u.username;

Results

4 rows returned

username	city
Ananya	Delhi
Meera	Bangalore
Neha	Jaipur
Vikram	Pune

---

Assignment 49: Purchase Distribution Buckets

Task: Bucket purchases into Low/Medium/High and show count per bucket.

SELECT
    CASE
        WHEN total_price < 5000 THEN 'Low'
        WHEN total_price <= 20000 THEN 'Medium'
        ELSE 'High'
    END AS bucket,
    COUNT(*) AS purchase_count
FROM User_Purchase
GROUP BY
    CASE
        WHEN total_price < 5000 THEN 'Low'
        WHEN total_price <= 20000 THEN 'Medium'
        ELSE 'High'
    END
ORDER BY
    CASE bucket
        WHEN 'Low' THEN 1
        WHEN 'Medium' THEN 2
        ELSE 3
    END;

Results

3 rows returned

bucket	purchase_count
Low	8
Medium	11
High	5

---

Assignment 50: Revenue Contribution Stability

Task: For each user, compute standard deviation of purchase values (lower = more stable).

SELECT
    u.username,
    u.city,
    ROUND(STDEV(up.total_price), 2) AS stdev_purchase_value
FROM [User] u
LEFT JOIN User_Purchase up
    ON u.userid = up.user_id
GROUP BY u.username, u.city
ORDER BY stdev_purchase_value;

Results

8 rows returned

username	city	stdev_purchase_value
Rahul	Mumbai	NaN
Meera	Bangalore	2121.32
Vikram	Pune	3069.61
Neha	Jaipur	7015.87
Ananya	Delhi	8568.35
Rohit	Kolkata	31355.38
Suresh	Hyderabad	40023.78
Arjun	Chennai	51335.95