20 Complex Programming Questions on MySQL

and Python
This document provides 20 complex programming questions split evenly between MySQL
(10) and Python (10). The MySQL section includes a reusable mini-dataset (DDL + sample
inserts) you should run first; all join questions refer to this schema.

MySQL — Setup (Run this first)

-- Drop if exists (idempotent setup)

DROP TABLE IF EXISTS Payments, Shipments, OrderItems, Orders, Products,

Suppliers, Customers, Employees, Departments;

-- Core entities

CREATE TABLE Customers (

customer_id INT PRIMARY KEY,

name VARCHAR(100),

segment ENUM('Consumer','Corporate','Small Business') NOT NULL,

city VARCHAR(80),

country VARCHAR(80)

);

CREATE TABLE Suppliers (

supplier_id INT PRIMARY KEY,

supplier_name VARCHAR(100),

city VARCHAR(80),

country VARCHAR(80)

);

CREATE TABLE Products (

product_id INT PRIMARY KEY,

 product_name VARCHAR(120),

category VARCHAR(80),

unit_price DECIMAL(10,2),

supplier_id INT,

FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)

);

CREATE TABLE Orders (

order_id INT PRIMARY KEY,

customer_id INT,

order_date DATE,

status ENUM('Pending','Shipped','Cancelled') NOT NULL,

FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)

);

CREATE TABLE OrderItems (

order_item_id INT PRIMARY KEY,

order_id INT,

product_id INT,

qty INT,

unit_price DECIMAL(10,2),

discount DECIMAL(4,2) DEFAULT 0, -- e.g., 0.10 = 10%

FOREIGN KEY (order_id) REFERENCES Orders(order_id),

FOREIGN KEY (product_id) REFERENCES Products(product_id)

);

CREATE TABLE Payments (

payment_id INT PRIMARY KEY,

 order_id INT,

amount DECIMAL(10,2),

payment_date DATE,

method ENUM('Card','UPI','Bank','COD') NOT NULL,

FOREIGN KEY (order_id) REFERENCES Orders(order_id)

);

CREATE TABLE Shipments (

shipment_id INT PRIMARY KEY,

order_id INT,

shipped_date DATE,

carrier VARCHAR(50),

freight DECIMAL(10,2),

FOREIGN KEY (order_id) REFERENCES Orders(order_id)

);

-- Org structure for employee joins

CREATE TABLE Departments (

dept_id INT PRIMARY KEY,

dept_name VARCHAR(80)

);

CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(100),

manager_id INT NULL,

dept_id INT,

city VARCHAR(80),
 salary DECIMAL(10,2),

FOREIGN KEY (manager_id) REFERENCES Employees(emp_id),

FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)

);

-- Sample data

INSERT INTO Customers VALUES

(1,'Asha Nair','Consumer','Mumbai','India'),

(2,'Ravi Kumar','Corporate','Bengaluru','India'),

(3,'Priya Shah','Small Business','Ahmedabad','India'),

(4,'John Doe','Consumer','New York','USA');

INSERT INTO Suppliers VALUES

(10,'ZenSupply','Mumbai','India'),

(11,'NorthStar Ltd','Chennai','India'),

(12,'BlueOcean','Dallas','USA');

INSERT INTO Products VALUES

(101,'USB-C Hub','Accessories',2499.00,10),

(102,'Mechanical Keyboard','Peripherals',5999.00,10),

(103,'4K Monitor','Displays',18999.00,12),

(104,'Ergo Chair','Furniture',12999.00,11);

INSERT INTO Orders VALUES

(5001,1,'2025-09-01','Shipped'),

(5002,2,'2025-09-05','Pending'),

(5003,2,'2025-09-07','Shipped'),

(5004,3,'2025-09-10','Cancelled'),
(5005,4,'2025-09-12','Shipped');

INSERT INTO OrderItems VALUES

(90001,5001,101,2,2499.00,0.10),

(90002,5001,102,1,5999.00,0),

(90003,5002,103,1,18999.00,0.05),

(90004,5003,101,3,2399.00,0.15), -- promo override price

(90005,5003,104,1,12999.00,0.05),

(90006,5004,104,2,12999.00,0),

(90007,5005,103,2,17999.00,0.10);

INSERT INTO Payments VALUES

(70001,5001, 2499*2*(1-0.10)+5999,'2025-09-02','Card'),

(70002,5003, 2399*3*(1-0.15)+12999*(1-0.05),'2025-09-08','UPI'),

(70003,5005, 17999*2*(1-0.10),'2025-09-13','Bank');

INSERT INTO Shipments VALUES

(80001,5001,'2025-09-02','Delhivery',350.00),

(80002,5003,'2025-09-08','Bluedart',500.00),

(80003,5005,'2025-09-13','FedEx',700.00);

INSERT INTO Departments VALUES

(1,'Sales'),(2,'Analytics'),(3,'Ops');

INSERT INTO Employees VALUES

(1010,'Meera Iyer',NULL,1,'Mumbai',180000.00),

(1011,'Kiran Rao',1010,1,'Pune',120000.00),

(1012,'Lakshmi Menon',1010,2,'Mumbai',135000.00),
(1013,'Arun Singh',1012,2,'Delhi',110000.00);

10 Complex MySQL Questions (Focus on Joins, Analytics, and Correctness)

1) Multi-table revenue with supplier attribution (INNER JOINs + calc)

Compute net line revenue per supplier_name across shipped orders only: net_line_revenue
= SUM(qty * unit_price * (1 - discount)). Exclude cancelled/pending; return supplier,
total_revenue, line_count.
Tables: Orders, OrderItems, Products, Suppliers.

2) Customer-level GMV vs. Payment reconciliation (JOIN + COALESCE)

For each customer, show: orders_count, gmv (sum of discounted line totals over all orders),
paid_amount (sum of payments), and delta = gmv - paid_amount. Include customers with no
payments.
Tables: Customers, Orders, OrderItems, Payments.

3) Product performance by market (JOIN + CASE)

For each product_name, compute shipped units and revenue split by country (India vs
Others) based on the customer’s country.
Tables: Orders -> Customers, OrderItems -> Products.

4) Orders without shipments (LEFT JOIN anti-join)

List all Pending orders that have no shipment yet (show order_id, customer, order_date).

5) Supplier fill rate (JOIN + ratio)

For each supplier, compute: order_lines (count of OrderItems referencing its Products),
shipped_lines (only those whose Order is Shipped), fill_rate = shipped_lines / order_lines.
Return suppliers with at least one order line.

6) Top N product by country using window functions (JOIN + DENSE_RANK)

Within each country, rank products by shipped revenue and return top 2 per country.
(MySQL 8+ window functions).

7) Manager → team salary rollup (self-join)

For each manager, show manager_name, team_size, team_total_salary for direct reports
only.
Tables: Employees self-join.

8) Multi-hop join: City parity between Suppliers and Customers (JOIN + EXISTS)
Return supplier–customer city pairs where both are in the same city and there is at least
one shipped order for a product from that supplier to that customer’s city. Show city,
supplier_name, distinct_customers_count.
Tables: Suppliers, Products, OrderItems, Orders, Customers.
9) Freight share and effective average freight per shipped order (JOIN + AVG)
For each carrier, return: shipped_orders, total_freight, avg_freight_per_item_line
(total_freight / number of item lines in shipped orders carried by that carrier).
Tables: Shipments, Orders, OrderItems.

10) Payment timeliness (JOIN + DATE DIFF + bucketing)

For shipped orders, compute days_to_pay = payment_date - shipped_date. Bucket orders
into <=1 day, 2–3 days, >3 days, and show counts per bucket.
Tables: Payments, Shipments, Orders.

Optional Starter Join Patterns

-- Supplier revenue on shipped orders

SELECT s.supplier_name,

SUM([Link] * oi.unit_price * (1 - [Link])) AS total_revenue,

COUNT(*) AS line_count

FROM Orders o

JOIN OrderItems oi ON oi.order_id = o.order_id

JOIN Products p ON p.product_id = oi.product_id

JOIN Suppliers s ON s.supplier_id = p.supplier_id

WHERE [Link] = 'Shipped'

GROUP BY s.supplier_name

ORDER BY total_revenue DESC;

-- Orders without shipment (pending only)

SELECT o.order_id, [Link] AS customer, o.order_date

FROM Orders o

JOIN Customers c ON c.customer_id=o.customer_id

LEFT JOIN Shipments s ON s.order_id=o.order_id

WHERE s.order_id IS NULL

AND [Link]='Pending';
10 Complex Python Programming Questions

1) LRU + TTL Cache (classes + dataclasses + heap or OrderedDict)

Implement an in-memory LRU cache with optional per-key TTL. API: get(key), set(key,
value, ttl=None), __len__. Expired keys should be invisible and lazily purged.

2) Streaming JSON lines validator (iterators + generators)

Read a multi-GB .jsonl file line-by-line (no full load), validate each JSON object against a
provided schema (subset rules you define), and write invalid lines with reasons to a log file.
Provide test cases.

3) Concurrency race demo & fix (threading vs. asyncio)

Simulate a race condition by incrementing a shared counter with 50 threads; show incorrect
totals. Then fix with [Link] (CPU-bound) and asyncio for an I/O-bound variant.
Explain GIL implications.

4) Data pipeline with backpressure (asyncio + queues)

Build 3-stage pipeline: producer → transformer → sink using [Link], with bounded
capacity to demonstrate backpressure. Support graceful cancellation.

5) Pluggable retry policy decorator (decorators + typing)

Write @retry(retries, backoff, retry_on) decorator supporting exponential backoff, retry on
certain exceptions or predicates, and jitter. Unit-test with a flaky function.

6) Context manager for transactional file writes

Implement atomic_write(path) that writes to a temp file, fsyncs, and renames on exit;
rollbacks on exception. Cross-platform safe (use tempfile, [Link]).

7) Inverted index with BM25 scoring (text processing)

Build a simple search engine: tokenize, normalize, remove stopwords; build posting lists;
implement BM25 ranking. Add query operators: "phrase", word1 AND word2, word1 OR
word2.

8) Vectorized ETL with Pandas → SQLite

Given CSV of sales lines, perform type coercion and null handling; derive columns (net, tax,
margin); window functions (rolling 7-day sums); write to SQLite with to_sql. Include timing
comparison vs. a pure Python loop.

9) Plugin architecture via entry points (packaging)

Create a CLI reportor that loads plugins via [Link].entry_points (or a registry).
Core app discovers and runs plugins that contribute report sections. Include minimal plugin
package.
10) Safe expression evaluator (AST)
Parse arithmetic expressions (with variables) using [Link], allow only safe nodes (BinOp,
Num/Constant, Name, UnaryOp), and evaluate with a provided env dict. Disallow attribute
access, calls, etc. Add unit tests.

Bonus: Cross-cutting “DB + Python” Integration Ideas

 Build a Python ETL that loads the MySQL dataset above, computes customer LTV offline
(Pandas or SQL), and writes a denormalized report table back to MySQL.
 Write a Python script that checks data quality rules (e.g., discount BETWEEN 0 AND 0.9,
qty > 0, foreign-key consistency) and sends a Slack/Email alert on violations.