Question 1

How to safely delete duplicate rows from a table while keeping only the oldest record?

Accepted Answer

### Removing Duplicate Records

Duplicate rows occur when data-seeding systems lack unique key constraints or face network retries. To clean the table, you must identify redundant rows and remove them while retaining the original entry.

#### The Solution Design:
1. **Use ROW_NUMBER():** We partition the data by duplicate identifiers (e.g., product details, departments, usernames) and sort them by ascending id or timestamp to assign sequential row indices.
2. **Isolate Redundancies:** Any row assigned a index greater than 1 represents a duplicate row.
3. **Perform Deletion:** Delete rows matching duplicate indexes inside a CTE, or query target sub-records using inline checks.

#### SQLite Production Pattern:
```sql
DELETE FROM products 
WHERE id NOT IN (
  SELECT MIN(id) 
  FROM products 
  GROUP BY sku, product_name
);
```

Solution SQL:
```sql
-- 1. Select and inspect current products
SELECT * FROM products;

-- 2. Let's find rows with duplicate names and prices, retaining only the lowest (original) ID
-- and delete the rest:
DELETE FROM products
WHERE id NOT IN (
  SELECT MIN(id)
  FROM products
  GROUP BY name, price
);

-- 3. Review final sanitized product inventory:
SELECT * FROM products;
```

Question 2

How to prevent critical Division by Zero errors during percentage and fraction calculations?

Accepted Answer

### Safe Fraction Calculations

In business intelligence, computing conversion rates, growth speeds, or refund margins often involves division. If the denominator is zero (e.g., zero signups, zero sales, zero department heads), the query halts with a division-by-zero error.

#### The Solution Design:
1. **Use NULLIF():** The `NULLIF(expression, value)` function compares two arguments. If they are equal, it returns `NULL`. Dividing by `NULL` returns `NULL` rather than failing.
2. **Handle NULL Returns:** We wrap the fraction inside `COALESCE(fraction, 0)` to convert potential `NULL` values back into a clean `0.0` margin display for dashboard reports.

#### SQLite Production Pattern:
```sql
SELECT COALESCE(amount / NULLIF(total, 0), 0) AS safe_ratio 
FROM transactions;
```

Solution SQL:
```sql
-- Simulating a conversion report. We calculate average order refund percentage.
-- If a user has 0 orders, normal division fails. We safeguard using NULLIF:
SELECT 
  u.id, 
  u.name,
  COUNT(o.id) as total_orders,
  SUM(CASE WHEN o.status = 'Cancelled' THEN 1 ELSE 0 END) as cancelled_orders,
  -- Safe division calculation:
  ROUND(
    COALESCE(
      CAST(SUM(CASE WHEN o.status = 'Cancelled' THEN 1 ELSE 0 END) AS REAL) * 100.0 / 
      NULLIF(COUNT(o.id), 0),
      0.0
    ), 
    2
  ) || '%' as cancellation_rate
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_orders ASC;
```

Question 3

How to retrieve employees who earn more than their respective department's average salary?

Accepted Answer

### Correlated Subqueries and Sub-Averages

Comparing individual row variables against group statistical metrics is a recurring pattern in workforce auditing, financial reporting, and fraud detection.

#### The Solution Design:
1. **Correlated Subquery:** We evaluate each employee row against a sub-selection. The subquery computes the department average and relies on a reference link to filter for the parent row's specific department.
2. **Alternative (Window Functions):** We can also map department statistics directly onto individual rows inside a CTE using `AVG(salary) OVER (PARTITION BY department_id)` and filter them in an outer query.

#### SQLite Production Pattern:
```sql
SELECT name, salary 
FROM employees outer_emp
WHERE salary > (
  SELECT AVG(salary) 
  FROM employees inner_emp
  WHERE inner_emp.department_id = outer_emp.department_id
);
```

Solution SQL:
```sql
-- 1. Find employees earning more than their department's average salary
-- using a correlated subquery:
SELECT 
  e.id, 
  e.name, 
  e.salary, 
  d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > (
  SELECT AVG(salary) 
  FROM employees 
  WHERE department_id = e.department_id
)
ORDER BY e.salary DESC;
```

Question 4

How to compute running cumulative totals and rolling averages for financial ledger auditing?

Accepted Answer

### Rolling Ledger Statistics

Financial accounting structures require running cumulative totals to audit active balances over time, trace daily sales trajectories, or monitor cashflow run-rates.

#### The Solution Design:
1. **Window Functions:** The `SUM(column) OVER (ORDER BY date_column)` statement computes rolling aggregates. It instructs the engine to sum all previous transactions in the sorted series up to the current row index.
2. **Alternative Self-Joins:** On older engines lacking window-function support, a rolling total requires a non-equi self-join on `t1.date >= t2.date` (which carries an exponential $O(N^2)$ computing penalty).

#### SQLite Production Pattern:
```sql
SELECT transaction_date, amount,
       SUM(amount) OVER (ORDER BY transaction_date ASC) as running_total
FROM ledgers;
```

Solution SQL:
```sql
-- Calculate running cumulative ledger balance and rolling transaction averages
-- for Checking accounts ordered by date:
SELECT 
  id,
  account_id,
  amount,
  transaction_date,
  -- Running total balance tracker:
  ROUND(
    SUM(amount) OVER (PARTITION BY account_id ORDER BY transaction_date ASC, id ASC),
    2
  ) as current_cumulative_balance,
  -- 3-transaction moving average:
  ROUND(
    AVG(amount) OVER (
      PARTITION BY account_id 
      ORDER BY transaction_date ASC, id ASC
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ),
    2
  ) as moving_avg_3_tx
FROM bank_transactions
WHERE account_id = 1
ORDER BY transaction_date ASC;
```

Question 5

How to query recursive hierarchical organizations (such as Manager-Employee reporting lines)?

Accepted Answer

### Hierarchical Organizational Graphs

Many datasets store hierarchical relationships where a row references another row in the same table (e.g., manager_id in employees, parent_id in product catalogs). Flattening these relationships requires recursive traversal.

#### The Solution Design:
1. **Recursive CTE:** A Common Table Expression can reference itself. We structure it using `WITH RECURSIVE` which contains two sections joined by a `UNION ALL`.
2. **Anchor Query:** Selects the root level node (e.g. CEO, top-level catalog node) as a starting point.
3. **Recursive Step:** Joins the CTE back to the base table on parent reference links to walk down the organizational tree structure, appending parent paths and calculation steps.

#### SQLite Production Pattern:
```sql
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e
  JOIN org_tree o ON e.manager_id = o.id
)
SELECT * FROM org_tree;
```

Solution SQL:
```sql
-- Traverse StaffCorp manager organizational hierarchies from top-to-bottom
-- using a self-referencing recursive CTE, mapping level depths and manager pathways:
WITH RECURSIVE org_chart AS (
  -- Anchor Member: Find the CEO (root)
  SELECT 
    id, 
    name, 
    role,
    manager_id,
    1 AS hierarchy_level,
    name AS reporting_path
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive Member: Join employee rows to their managers from the CTE
  SELECT 
    e.id, 
    e.name, 
    e.role,
    e.manager_id,
    o.hierarchy_level + 1,
    o.reporting_path || ' -> ' || e.name
  FROM employees e
  JOIN org_chart o ON e.manager_id = o.id
)
SELECT 
  hierarchy_level,
  name,
  role,
  reporting_path
FROM org_chart
ORDER BY hierarchy_level ASC, name ASC;
```

Question 6

How to retrieve only the most recent order for each customer in a multi-table join?

Accepted Answer

### Isolating First Row Per Group

Selecting the latest, highest, or lowest row per group is a notorious relational query challenge. It often arises when identifying a customer's active subscription, latest transaction, or maximum purchase.

#### The Solution Design:
1. **Use ROW_NUMBER():** The most robust, modern approach utilizes window partitioning. We instruct the system to partition rows by client ID and rank them chronologically.
2. **Filter Rank index:** Inside a subquery or a CTE, we filter for records that carry a ranked row index equal to `1`.
3. **Correlated Alternative:** For older database versions, we perform a subquery checking where the order date matches the `MAX(order_date)` of that customer.

#### SQLite Production Pattern:
```sql
WITH ranked_orders AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rn
  FROM orders
)
SELECT * FROM ranked_orders WHERE rn = 1;
```

Solution SQL:
```sql
-- Retrieve the absolute most recent order details for each registered user:
WITH chronological_index AS (
  SELECT 
    id AS order_id,
    user_id,
    total_amount,
    order_date,
    status,
    -- Window ranking assigned per customer group:
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rank_index
  FROM orders
)
SELECT 
  u.id AS customer_id,
  u.name AS customer_name,
  o.order_id,
  o.total_amount,
  o.order_date,
  o.status
FROM users u
INNER JOIN chronological_index o ON u.id = o.user_id
WHERE o.rank_index = 1
ORDER BY o.order_date DESC;
```

Question 7

How to group-concatenate multiple rows into a single comma-separated list?

Accepted Answer

### Row Aggregation Into Flat Lists

Often, reporting dashboards need to display related child records grouped inside a single cell (e.g., listing a playlist's tracks, a user's items, or a company's office tags separated by commas).

#### The Solution Design:
1. **Use GROUP_CONCAT():** Standard SQL engines use built-in concatenation aggregates. In SQLite, MySQL, and PostgreSQL, the functions are `GROUP_CONCAT()`, `GROUP_CONCAT()`, and `STRING_AGG()` respectively.
2. **Delimiter declaration:** We declare the target text delimiter as the secondary parameter (e.g. `', '` or `' | '`).
3. **GROUP BY constraints:** We collapse rows around the parent ID to bundle child rows into the string output.

#### SQLite Production Pattern:
```sql
SELECT user_id, GROUP_CONCAT(product_name, ', ') AS item_list
FROM orders
GROUP BY user_id;
```

Solution SQL:
```sql
-- Compile all songs in each playlist into a single comma-separated text cell
-- sorted by artist, collapsed via playlist group ID:
SELECT 
  p.id AS playlist_id,
  p.name AS playlist_title,
  p.description,
  COUNT(s.id) AS total_tracks,
  -- Collapsing tracks list:
  GROUP_CONCAT(s.title, ' | ') AS bundled_tracks_preview
FROM playlists p
LEFT JOIN playlist_tracks pt ON p.id = pt.playlist_id
LEFT JOIN songs s ON pt.song_id = s.id
GROUP BY p.id, p.name, p.description
ORDER BY total_tracks DESC;
```

Question 8

How to locate records in a parent table that do not have any child references in a secondary table?

Accepted Answer

### Spotting Orphan and Inactive Records

Finding items in Table A that are missing in Table B is a standard audit task. It helps identify inactive users, products with zero sales, or employees without department assignments.

#### The Solution Design:
1. **LEFT JOIN Pattern:** We join the parent table to the child table. This preserves all parent rows. We filter where the child key is `NULL`.
2. **NOT EXISTS Pattern:** Often cleaner and faster on modern optimizers. It checks each parent row and halts as soon as it finds a match, using less memory.
3. **Performance Difference:** On large datasets, `NOT EXISTS` avoids building a virtual joined table, making it highly optimized.

#### SQLite Production Pattern:
```sql
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
```

Solution SQL:
```sql
-- Identify cold/inactive users who have registered but never placed an order
-- using BOTH LEFT JOIN and NOT EXISTS patterns for evaluation:
SELECT 
  u.id,
  u.name,
  u.email,
  u.created_at
FROM users u
WHERE NOT EXISTS (
  SELECT 1 
  FROM orders o 
  WHERE o.user_id = u.id
)
ORDER BY u.created_at ASC;
```

Question 9

How to run conditional aggregates to count or sum rows matching specific filters?

Accepted Answer

### Slicing Metrics Using Conditional Aggregates

Business intelligence requires reporting metrics across multiple dimensions simultaneously (e.g. Total sales, Approved sales, Pending sales) in a single row summary.

#### The Solution Design:
1. **Use CASE WHEN Inside Aggregates:** We insert logic checks inside `SUM()` or `COUNT()`. If the conditions match, we yield values to aggregate; otherwise, we return `0` or `NULL`.
2. **Avoid WHERE Exclusions:** Standard WHERE clauses filter out rows early. This drops other dimensions from our final report summary.

#### SQLite Production Pattern:
```sql
SELECT 
  SUM(CASE WHEN status = 'Paid' THEN amount ELSE 0 END) AS paid_revenue,
  SUM(CASE WHEN status = 'Refunded' THEN amount ELSE 0 END) AS refund_leakage
FROM sales;
```

Solution SQL:
```sql
-- Generate an executive loan ledger summary, tracking total loans, principal amounts,
-- and delinquency metrics in a single group row:
SELECT 
  customer_id,
  COUNT(id) AS active_loans_count,
  -- 1. Grand cumulative principal:
  SUM(loan_amount) AS total_borrowed,
  -- 2. Summing ONLY high-interest risk loans:
  SUM(CASE WHEN interest_rate >= 8.0 THEN loan_amount ELSE 0 END) AS high_interest_borrowed,
  -- 3. Counting delinquent warnings:
  SUM(CASE WHEN loan_amount >= 30000 THEN 1 ELSE 0 END) AS priority_debtor_warnings
FROM bank_loans
GROUP BY customer_id
ORDER BY total_borrowed DESC;
```

Question 10

How to detect overlapping date intervals in calendars, booking records, or employment logs?

Accepted Answer

### Overlapping Durations and Time Ranges Checking for date overlaps is a core task in booking calendars, warehouse reservations, and employment logs to prevent double bookings. #### The Solution Design: 1. **Overlapping Logic Formula:** Two date ranges (A and B) overlap if `StartA <= EndB` AND `EndA >= StartB`. 2. **Exclude Self-Matching:** When joining a table to itself, exclude matching IDs to prevent rows from flagging themselves as overlaps. #### SQLite Production Pattern: ```sql SELECT t1.id, t2.id FROM schedules t1 JOIN schedules t2 ON t1.id < t2.id WHERE t1.start_date <= t2.end_date AND t1.end_date >= t2.start_date; ``` Solution SQL: ```sql -- Auditor test: Search for employees assigned overlapping roles -- in department management schedules using range bound checks: SELECT e1.name AS employee_1, e1.role AS role_1, e2.name AS employee_2, e2.role AS role_2, e1.department_id FROM employees e1 JOIN employees e2 ON e1.department_id = e2.department_id AND e1.id < e2.id -- Check overlap dates: WHERE (e1.hire_date <= '2026-12-31') AND ('2020-01-01' >= e2.hire_date) LIMIT 5; ```