Advanced

4. User_Profile

Extended user attributes

CREATE TABLE User_Profile (
    profile_id INT IDENTITY(1,1) PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,
    email VARCHAR(200),
    phone VARCHAR(20),
    created_at DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (user_id) REFERENCES [User](userid)
);

---

5. User_Address

Multiple addresses per user

CREATE TABLE User_Address (
    address_id INT IDENTITY(1,1) PRIMARY KEY,
    user_id INT NOT NULL,
    address_line VARCHAR(300),
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    pincode VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES [User](userid)
);

---

6. User_Login_History

Tracks login behavior

CREATE TABLE User_Login_History (
    login_id INT IDENTITY(1,1) PRIMARY KEY,
    user_id INT NOT NULL,
    login_time DATETIME,
    ip_address VARCHAR(50),
    device VARCHAR(100),
    FOREIGN KEY (user_id) REFERENCES [User](userid)
);

---

7. Product_Category

CREATE TABLE Product_Category (
    category_id INT IDENTITY(1,1) PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

---

8. Product_Category_Map

Many-to-many: products ↔ categories

CREATE TABLE Product_Category_Map (
    product_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (product_id, category_id),
    FOREIGN KEY (product_id) REFERENCES Product(productid),
    FOREIGN KEY (category_id) REFERENCES Product_Category(category_id)
);

---

9. Product_Inventory

CREATE TABLE Product_Inventory (
    inventory_id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL,
    stock_quantity INT,
    last_updated DATETIME,
    FOREIGN KEY (product_id) REFERENCES Product(productid)
);

---

10. Product_Price_History

CREATE TABLE Product_Price_History (
    price_history_id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL,
    price FLOAT,
    effective_from DATETIME,
    FOREIGN KEY (product_id) REFERENCES Product(productid)
);

---

11. Order_Header

Logical order abstraction

CREATE TABLE Order_Header (
    order_id INT IDENTITY(1,1) PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATETIME,
    order_status VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES [User](userid)
);

---

12. Order_Item

CREATE TABLE Order_Item (
    order_item_id INT IDENTITY(1,1) PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    unit_price FLOAT,
    FOREIGN KEY (order_id) REFERENCES Order_Header(order_id),
    FOREIGN KEY (product_id) REFERENCES Product(productid)
);

---

13. Payment

CREATE TABLE Payment (
    payment_id INT IDENTITY(1,1) PRIMARY KEY,
    order_id INT NOT NULL,
    payment_method VARCHAR(50),
    payment_status VARCHAR(50),
    amount FLOAT,
    payment_date DATETIME,
    FOREIGN KEY (order_id) REFERENCES Order_Header(order_id)
);

---

14. Shipment

CREATE TABLE Shipment (
    shipment_id INT IDENTITY(1,1) PRIMARY KEY,
    order_id INT NOT NULL,
    shipped_date DATETIME,
    delivery_date DATETIME,
    carrier VARCHAR(100),
    tracking_number VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES Order_Header(order_id)
);

---

15. Review

CREATE TABLE Review (
    review_id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT CHECK (rating BETWEEN 1 AND 5),
    comment VARCHAR(500),
    review_date DATETIME,
    FOREIGN KEY (product_id) REFERENCES Product(productid),
    FOREIGN KEY (user_id) REFERENCES [User](userid)
);

---

16. Wishlist

CREATE TABLE Wishlist (
    wishlist_id INT IDENTITY(1,1) PRIMARY KEY,
    user_id INT NOT NULL,
    created_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES [User](userid)
);

---

17. Wishlist_Item

CREATE TABLE Wishlist_Item (
    wishlist_id INT NOT NULL,
    product_id INT NOT NULL,
    PRIMARY KEY (wishlist_id, product_id),
    FOREIGN KEY (wishlist_id) REFERENCES Wishlist(wishlist_id),
    FOREIGN KEY (product_id) REFERENCES Product(productid)
);

---

18. Coupon

CREATE TABLE Coupon (
    coupon_id INT IDENTITY(1,1) PRIMARY KEY,
    coupon_code VARCHAR(50),
    discount_percent FLOAT,
    expiry_date DATETIME
);

---

19. Order_Coupon

CREATE TABLE Order_Coupon (
    order_id INT NOT NULL,
    coupon_id INT NOT NULL,
    PRIMARY KEY (order_id, coupon_id),
    FOREIGN KEY (order_id) REFERENCES Order_Header(order_id),
    FOREIGN KEY (coupon_id) REFERENCES Coupon(coupon_id)
);

---

20. Audit_Log

CREATE TABLE Audit_Log (
    audit_id INT IDENTITY(1,1) PRIMARY KEY,
    entity_name VARCHAR(100),
    entity_id INT,
    action VARCHAR(50),
    action_date DATETIME,
    performed_by INT
);

---

Sample Entrie

Understood. Below is a clean, FK-safe data seeding script with 10 rows per table, using multi-row INSERTs (industry-standard practice). This keeps it readable while still giving you 10 records per table.

Assumptions • IDENTITY columns auto-generate IDs • IDs are referenced sequentially (1–10) • Script is executed in order

---

TABLE 1 – User (10 rows)

INSERT INTO [User] (username, city) VALUES
('Arjun','Chennai'),
('Meera','Bangalore'),
('Rahul','Mumbai'),
('Sneha','Pune'),
('Karthik','Coimbatore'),
('Anita','Delhi'),
('Ravi','Hyderabad'),
('Priya','Trichy'),
('Suresh','Madurai'),
('Neha','Noida');

---

TABLE 2 – Product (10 rows)

INSERT INTO Product (title, price) VALUES
('Laptop',75000),
('Mouse',1200),
('Keyboard',4500),
('Monitor',18000),
('USB Hub',2500),
('Headphones',6000),
('Webcam',3500),
('Desk Lamp',1800),
('Chair',12000),
('Power Bank',2200);

---

TABLE 3 – User_Purchase (10 rows)

INSERT INTO User_Purchase (product_id, user_id, purchase_date, quantity, total_price) VALUES
(1,1,'2025-01-01',1,75000),
(2,2,'2025-01-02',2,2400),
(3,3,'2025-01-03',1,4500),
(4,4,'2025-01-04',1,18000),
(5,5,'2025-01-05',3,7500),
(6,6,'2025-01-06',1,6000),
(7,7,'2025-01-07',2,7000),
(8,8,'2025-01-08',1,1800),
(9,9,'2025-01-09',1,12000),
(10,10,'2025-01-10',2,4400);

---

TABLE 4 – User_Profile (10 rows)

INSERT INTO User_Profile (user_id,email,phone) VALUES
(1,'[e-mail]','900000001'),
(2,'[e-mail]','900000002'),
(3,'[e-mail]','900000003'),
(4,'[e-mail]','900000004'),
(5,'[e-mail]','900000005'),
(6,'[e-mail]','900000006'),
(7,'[e-mail]','900000007'),
(8,'[e-mail]','900000008'),
(9,'[e-mail]','900000009'),
(10,'[e-mail]','900000010');

---

TABLE 5 – User_Address (10 rows)

INSERT INTO User_Address (user_id,address_line,city,state,country,pincode) VALUES
(1,'Street 1','Chennai','TN','India','600001'),
(2,'Street 2','Bangalore','KA','India','560001'),
(3,'Street 3','Mumbai','MH','India','400001'),
(4,'Street 4','Pune','MH','India','411001'),
(5,'Street 5','Coimbatore','TN','India','641001'),
(6,'Street 6','Delhi','DL','India','110001'),
(7,'Street 7','Hyderabad','TS','India','500001'),
(8,'Street 8','Trichy','TN','India','620001'),
(9,'Street 9','Madurai','TN','India','625001'),
(10,'Street 10','Noida','UP','India','201301');

---

TABLE 6 – User_Login_History (10 rows)

INSERT INTO User_Login_History (user_id,login_time,ip_address,device) VALUES
(1,GETDATE(),'10.0.0.1','Mobile'),
(2,GETDATE(),'10.0.0.2','Laptop'),
(3,GETDATE(),'10.0.0.3','Tablet'),
(4,GETDATE(),'10.0.0.4','Mobile'),
(5,GETDATE(),'10.0.0.5','Laptop'),
(6,GETDATE(),'10.0.0.6','Desktop'),
(7,GETDATE(),'10.0.0.7','Mobile'),
(8,GETDATE(),'10.0.0.8','Laptop'),
(9,GETDATE(),'10.0.0.9','Tablet'),
(10,GETDATE(),'10.0.0.10','Desktop');

---

TABLE 7 – Product_Category (10 rows)

INSERT INTO Product_Category (category_name) VALUES
('Electronics'),
('Accessories'),
('Office'),
('Gaming'),
('Home'),
('Mobile'),
('Audio'),
('Video'),
('Furniture'),
('Power');

---

TABLE 8 – Product_Category_Map (10 rows)

INSERT INTO Product_Category_Map VALUES
(1,1),(2,2),(3,2),(4,1),(5,2),
(6,7),(7,8),(8,5),(9,9),(10,10);

---

TABLE 9 – Product_Inventory (10 rows)

INSERT INTO Product_Inventory (product_id,stock_quantity,last_updated) VALUES
(1,50,GETDATE()),(2,200,GETDATE()),(3,100,GETDATE()),
(4,30,GETDATE()),(5,80,GETDATE()),(6,60,GETDATE()),
(7,40,GETDATE()),(8,90,GETDATE()),(9,20,GETDATE()),(10,70,GETDATE());

---

TABLE 10 – Product_Price_History (10 rows)

INSERT INTO Product_Price_History (product_id,price,effective_from) VALUES
(1,70000,'2024-01-01'),
(2,1000,'2024-01-01'),
(3,4200,'2024-01-01'),
(4,17000,'2024-01-01'),
(5,2300,'2024-01-01'),
(6,5800,'2024-01-01'),
(7,3300,'2024-01-01'),
(8,1600,'2024-01-01'),
(9,11500,'2024-01-01'),
(10,2000,'2024-01-01');

---

TABLE 11 – Order_Header (10 rows)

INSERT INTO Order_Header (user_id,order_date,order_status) VALUES
(1,GETDATE(),'Delivered'),
(2,GETDATE(),'Delivered'),
(3,GETDATE(),'Shipped'),
(4,GETDATE(),'Pending'),
(5,GETDATE(),'Delivered'),
(6,GETDATE(),'Delivered'),
(7,GETDATE(),'Shipped'),
(8,GETDATE(),'Pending'),
(9,GETDATE(),'Delivered'),
(10,GETDATE(),'Delivered');

---

TABLE 12 – Order_Item (10 rows)

INSERT INTO Order_Item (order_id,product_id,quantity,unit_price) VALUES
(1,1,1,75000),(2,2,2,1200),(3,3,1,4500),(4,4,1,18000),
(5,5,3,2500),(6,6,1,6000),(7,7,2,3500),(8,8,1,1800),
(9,9,1,12000),(10,10,2,2200);

---

TABLE 13 – Payment (10 rows)

INSERT INTO Payment (order_id,payment_method,payment_status,amount,payment_date) VALUES
(1,'UPI','Success',75000,GETDATE()),
(2,'Card','Success',2400,GETDATE()),
(3,'UPI','Success',4500,GETDATE()),
(4,'NetBanking','Pending',18000,GETDATE()),
(5,'UPI','Success',7500,GETDATE()),
(6,'Card','Success',6000,GETDATE()),
(7,'UPI','Success',7000,GETDATE()),
(8,'UPI','Pending',1800,GETDATE()),
(9,'Card','Success',12000,GETDATE()),
(10,'UPI','Success',4400,GETDATE());

---

TABLE 14 – Shipment (10 rows)

INSERT INTO Shipment (order_id,shipped_date,delivery_date,carrier,tracking_number) VALUES
(1,GETDATE(),GETDATE(),'BlueDart','TRK1'),
(2,GETDATE(),GETDATE(),'Delhivery','TRK2'),
(3,GETDATE(),NULL,'Ecom','TRK3'),
(4,NULL,NULL,'Ecom','TRK4'),
(5,GETDATE(),GETDATE(),'BlueDart','TRK5'),
(6,GETDATE(),GETDATE(),'DTDC','TRK6'),
(7,GETDATE(),NULL,'DTDC','TRK7'),
(8,NULL,NULL,'Ecom','TRK8'),
(9,GETDATE(),GETDATE(),'BlueDart','TRK9'),
(10,GETDATE(),GETDATE(),'Delhivery','TRK10');

---

TABLE 15 – Review (10 rows)

INSERT INTO Review (product_id,user_id,rating,comment,review_date) VALUES
(1,1,5,'Excellent',GETDATE()),
(2,2,4,'Good',GETDATE()),
(3,3,5,'Perfect',GETDATE()),
(4,4,3,'Average',GETDATE()),
(5,5,4,'Nice',GETDATE()),
(6,6,5,'Super',GETDATE()),
(7,7,4,'Good',GETDATE()),
(8,8,3,'OK',GETDATE()),
(9,9,5,'Great',GETDATE()),
(10,10,4,'Value',GETDATE());

---

TABLE 16 – Wishlist (10 rows)

INSERT INTO Wishlist (user_id,created_at) VALUES
(1,GETDATE()),(2,GETDATE()),(3,GETDATE()),(4,GETDATE()),(5,GETDATE()),
(6,GETDATE()),(7,GETDATE()),(8,GETDATE()),(9,GETDATE()),(10,GETDATE());

---

TABLE 17 – Wishlist_Item (10 rows)

INSERT INTO Wishlist_Item VALUES
(1,2),(2,3),(3,4),(4,5),(5,6),
(6,7),(7,8),(8,9),(9,10),(10,1);

---

TABLE 18 – Coupon (10 rows)

INSERT INTO Coupon (coupon_code,discount_percent,expiry_date) VALUES
('NEW10',10,'2025-12-31'),
('SALE20',20,'2025-12-31'),
('SAVE5',5,'2025-12-31'),
('BIG15',15,'2025-12-31'),
('FEST25',25,'2025-12-31'),
('DISC30',30,'2025-12-31'),
('OFFER10',10,'2025-12-31'),
('PROMO5',5,'2025-12-31'),
('FLAT20',20,'2025-12-31'),
('MAX50',50,'2025-12-31');

---

TABLE 19 – Order_Coupon (10 rows)

INSERT INTO Order_Coupon VALUES
(1,1),(2,2),(3,3),(4,4),(5,5),
(6,6),(7,7),(8,8),(9,9),(10,10);

---

TABLE 20 – Audit_Log (10 rows)

INSERT INTO Audit_Log (entity_name,entity_id,action,action_date,performed_by) VALUES
('User',1,'INSERT',GETDATE(),1),
('Product',2,'UPDATE',GETDATE(),2),
('Order',3,'INSERT',GETDATE(),3),
('Payment',4,'INSERT',GETDATE(),4),
('Shipment',5,'UPDATE',GETDATE(),5),
('Review',6,'INSERT',GETDATE(),6),
('Wishlist',7,'INSERT',GETDATE(),7),
('Coupon',8,'INSERT',GETDATE(),8),
('Order',9,'UPDATE',GETDATE(),9),
('User',10,'UPDATE',GETDATE(),10);

---

Assginments

Assignment 1: Customer 360 View

Objective: Create a query that shows a single consolidated view per user, including:

• username

• city

• email

• total number of orders

• total amount spent

• last purchase date

Skills tested: joins, aggregation, grouping

---

Assignment 2: Revenue & Fulfillment Analysis

Objective: List all orders with:

• order id

• username

• order status

• payment status

• shipment carrier

• delivery date

Filter only orders that are paid but not yet delivered.

Skills tested: multi-table joins, filtering, business logic

---

Assignment 3: Product Performance Dashboard

Objective: For each product, calculate:

• total quantity sold

• total revenue

• average rating

• current stock quantity

Sort products by total revenue descending.

Skills tested: joins, aggregates, left joins, ordering

---

Assignment 4: Marketing Effectiveness (Coupons)

Objective: Analyze coupon usage and show:

• coupon code

• number of orders using it

• total revenue generated from those orders

• average order value per coupon

Skills tested: many-to-many joins, aggregation

---

Assignment 5: User Engagement & Behavior

Objective: Identify users who:

• logged in at least 3 times

• placed at least 1 order

• added at least 1 product to wishlist

• wrote at least 1 review

Return username and counts for each activity.

Skills tested: subqueries / CTEs, HAVING, behavioral analysis

---

Assignment 6: High-Value Customers (HVC)

Objective: Identify users who:

• have placed more than 1 order, and

• whose total spending is above the average user spending.

Return:

• username

• city

• total orders

• total spending

Skills tested: aggregation, subqueries / CTEs, HAVING

---

Assignment 7: Order-to-Delivery Efficiency

Objective: Calculate the average delivery time (in days) for each carrier.

Return:

• carrier

• total shipments

• average delivery days

Exclude orders that are not yet delivered.

Skills tested: date functions, joins, filtering

---

Assignment 8: Product Price Volatility

Objective: For each product, calculate:

• minimum historical price

• maximum historical price

• price fluctuation percentage

Fluctuation % = [ \frac{max_price - min_price}{min_price} \times 100 ]

Skills tested: aggregation, derived metrics

---

Assignment 9: User Purchase Funnel

Objective: For each user, show whether they have:

• registered (exists in User)

• placed an order

• completed payment

• received shipment

Return one row per user with Yes / No flags.

Skills tested: LEFT JOINs, CASE statements

---

Assignment 10: Cross-Sell Opportunity Detection

Objective: Find product pairs that are:

• purchased by the same users, and

• appear together in different orders by those users.

Return:

• product A

• product B

• number of common users

Skills tested: self-joins, DISTINCT logic, analytics reasoning

---

Assignment 11: Repeat Purchase Behavior

Objective: Identify users who have purchased the same product more than once across different orders.

Skills tested: joins, grouping, HAVING

---

Assignment 12: Revenue Leakage Detection

Objective: Find orders where:

• payment status is Success

• but no shipment record exists

Return order id, user, payment amount.

Skills tested: LEFT JOIN, NULL checks

---

Assignment 13: Inventory Risk Analysis

Objective: List products where:

• current stock quantity is less than total quantity sold

Skills tested: aggregation, joins, comparison logic

---

Assignment 14: Review Bias Detection

Objective: Find users whose average rating is:

• at least 1 point higher than the overall average rating

Skills tested: aggregation, subqueries

---

Assignment 15: Time-to-Purchase Analysis

Objective: For each user, calculate the number of days between account creation (User_Profile.created_at) and their first order date.

Skills tested: date arithmetic, MIN aggregation

---

Assignment 16: Coupon Effectiveness Over Time

Objective: Show monthly coupon usage with:

• month

• number of orders

• total discounted revenue (assume discount applies to full order amount)

Skills tested: time grouping, joins, calculations

---

Assignment 17: Product Cannibalization

Objective: Find products where:

• a price drop (from Product_Price_History) is followed by

• a measurable increase in quantity sold

Skills tested: temporal joins, analytics reasoning

---

Assignment 18: Dormant Users

Objective: Identify users who:

• have registered

• but have not placed any order in the last 90 days

Skills tested: date filters, anti-joins

---

Assignment 19: End-to-End Order Trace

Objective: Create a query that shows, per order:

• user

• products purchased

• payment status

• shipment status

• coupon used (if any)

Skills tested: multi-table joins, optional relationships

---

Assignment 20: Customer Lifetime Value (CLV)

Objective: Calculate CLV per user using:

CLV=Total Revenue×Purchase Frequency

Skills tested: aggregation, derived metrics

---

Below are 10 more advanced SQL assignments (Assignments 21–30) built strictly on your existing 20-table schema. These focus on analytics depth, reasoning, and production-style querying.

---

Assignment 21: Order Completion Rate per User

Objective: For each user, calculate:

• total orders placed

• total orders delivered

• order completion rate (%)

Skills tested: conditional aggregation, joins

---

Assignment 22: Revenue by Payment Method

Objective: Show:

• payment method

• total number of payments

• total revenue

• average payment amount

Skills tested: grouping, aggregation

---

Assignment 23: Products with No Reviews but High Sales

Objective: Identify products that:

• have zero reviews

• but have total revenue above the average product revenue

Skills tested: LEFT JOIN, aggregation, subqueries

---

Assignment 24: User Order Interval Analysis

Objective: For each user, calculate the average number of days between consecutive orders.

Skills tested: window functions (LAG), date arithmetic

---

Assignment 25: Carrier Performance Consistency

Objective: Find carriers where:

• delivery time variance is low (stable delivery)

• average delivery time is under 5 days

Skills tested: aggregation, variance/stddev functions

---

Assignment 26: Category Revenue Contribution

Objective: For each product category, calculate:

• total revenue

• percentage contribution to overall revenue

Skills tested: many-to-many joins, percentage calculation

---

Assignment 27: Users Who Abandoned Payment

Objective: Identify users who:

• placed orders

• but have only failed or pending payments

Skills tested: joins, HAVING logic

---

Assignment 28: Most Influential Reviewers

Objective: Find users whose reviews are:

• above the overall average rating

• and who reviewed at least 3 products

Skills tested: aggregation, filtering

---

Assignment 29: Inventory Turnover Ratio

Objective: For each product, calculate the inventory turnover ratio:

Turnover = Average InventoryTotal / Quantity Sold​

Skills tested: aggregation, business metrics

---

Assignment 30: End-of-Month Revenue Spike Detection

Objective: Detect months where:

• revenue in the last 5 days of the month

• exceeds the first 5 days by more than 30%

Skills tested: time-based grouping, conditional aggregation

---

Below are 10 expert-level SQL assignments (Assignments 31–40) built strictly on your existing 20-table schema. These emphasize advanced analytics, temporal reasoning, and production-grade thinking.

---

Assignment 31: Customer Churn Indicator

Objective: Identify users whose last order date is older than 120 days and who have no pending shipments.

Skills tested: date filters, anti-joins

---

Assignment 32: Revenue Concentration Risk

Objective: Calculate the percentage of total revenue contributed by the top 10% of users.

Skills tested: window functions, percentile logic

---

Assignment 33: Product Seasonality Detection

Objective: Find products whose sales spike (≥40% increase) in any month compared to their own average monthly sales.

Skills tested: time grouping, comparisons, subqueries

---

Assignment 34: Order Processing Bottleneck

Objective: Identify orders where:

• payment was successful

• but shipment was created more than 3 days later

Skills tested: date arithmetic, joins

---

Assignment 35: User Loyalty Score

Objective: Compute a loyalty score per user using: [ \text{Score} = (2 \times \text{Orders}) + (1 \times \text{Reviews}) + (3 \times \text{Wishlist Items}) ]

Skills tested: multi-table aggregation

---

Assignment 36: Price Sensitivity Analysis

Objective: For each product, analyze whether:

• a price reduction

• leads to a statistically significant increase in quantity sold (before vs after).

Skills tested: temporal segmentation, analytical reasoning

---

Assignment 37: Fulfillment SLA Compliance

Objective: Calculate the SLA compliance rate per carrier:

• Delivered within ≤5 days

Skills tested: conditional aggregation, joins

---

Assignment 38: Cross-Category Buyer Analysis

Objective: Identify users who purchased products from at least 3 different categories.

Skills tested: many-to-many joins, DISTINCT counts

---

Assignment 39: Revenue Recovery Opportunity

Objective: Find orders that:

• failed payment initially

• but later succeeded Return recovery rate.

Skills tested: sequencing, status transitions

---

Assignment 40: End-to-End Conversion Funnel

Objective: Build a funnel showing:

• registered users

• users with orders

• users with successful payments

• users with delivered shipments

Skills tested: funnel analytics, LEFT JOIN logic

---

Assignment 41: Customer Purchase Momentum

Objective: For each user, determine whether their purchase frequency is accelerating, stable, or declining over time.

Skills tested: window functions, trend analysis

---

Assignment 42: Revenue Dependency Index

Objective: For each product, calculate what percentage of its revenue comes from repeat buyers vs first-time buyers.

Skills tested: cohort analysis, joins, aggregation

---

Assignment 43: Order Fragmentation Risk

Objective: Identify users who place many small orders instead of fewer large ones (e.g., average order value < overall average but order count > overall average).

Skills tested: comparative aggregation

---

Assignment 44: Supply Chain Stress Detection

Objective: Detect products where:

• inventory is low

• sales velocity is high

• shipment delays are frequent

Skills tested: multi-domain joins, business logic

---

Assignment 45: Review Impact on Sales

Objective: Analyze whether products receive higher sales after positive reviews (rating ≥ 4).

Skills tested: temporal joins, before/after comparison

---

Assignment 46: Coupon Abuse Detection

Objective: Identify users who:

• repeatedly use high-discount coupons

• across multiple orders

• within a short time window.

Skills tested: joins, filtering, time windows

---

Assignment 47: Multi-Channel Payment Reliability

Objective: For each payment method, calculate:

• success rate

• average retry count per order (inferred from payment attempts)

Skills tested: aggregation, inferred metrics

---

Assignment 48: Product Portfolio Risk

Objective: Find categories where:

• revenue is concentrated in ≤2 products

• contributing ≥70% of category revenue.

Skills tested: ranking, cumulative sums

---

Assignment 49: Silent Drop-Off Detection

Objective: Identify users who:

• added items to wishlist

• logged in multiple times

• but never completed a purchase.

Skills tested: anti-joins, behavioral analysis

---

Assignment 50: Executive KPI Snapshot

Objective: Produce a single query that outputs:

• total users

• active users (last 30 days)

• total revenue

• average order value

• delivery success rate

• top product by revenue

Skills tested: multi-metric aggregation, executive reporting

---

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