How do I practice coding and algorithm questions?

Use PracHub's coding console to write, test, and debug your solutions in Python or JavaScript. View hints, test against sample inputs, and compare with official solutions.

What difficulty level is this coding question?

This is a medium difficulty Coding & Algorithms question, commonly asked during Technical Screen rounds at Tesla.

What role is this question designed for?

This question is commonly asked for Data Engineer candidates at Tesla during technical interviews.

Write SQL Data Transformation Queries

Company: Tesla

Role: Data Engineer

Category: Coding & Algorithms

Difficulty: medium

Interview Round: Technical Screen

Use SQL to solve the following data transformation tasks. Assume standard SQL features are available, including window functions and recursive common table expressions where needed. 1. Monthly-reset cumulative sales Table: `sales` Columns: - `date` - `store_id` - `amount` Compute cumulative sales by store ordered by date, but reset the running total at the start of each new calendar month. If there are multiple rows for the same `store_id` and `date`, aggregate them before computing the running total. Expected output columns: - `store_id` - `date` - `total_amount` 2. Full reporting hierarchy Table: `organization` Columns: - `employee_id` - `manager_id` - `name` Find all employees who report to the manager with ID `1`, including both direct and indirect reports at any depth. Return each employee's hierarchy level and a full hierarchy path from manager `1` to that employee. The hierarchy path may be represented as a delimiter-separated list of employee IDs or names. Expected output columns: - `employee_id` - `manager_id` - `name` - `level` - `hierarchy_path` 3. Conditional order metrics by date Table: `orders` Columns: - `order_id` - `status`, where status is one of `pending`, `completed`, or `cancelled` - `amount` - `date` Write a single query that scans the table once and computes the following metrics by date: - `total_revenue`: sum of `amount` for completed orders only - `pending_order_count`: count of pending orders - `cancelled_order_count`: count of cancelled orders

Quick Answer: This question evaluates proficiency in SQL data transformation and aggregation, testing competencies such as monthly-reset running totals, hierarchical recursion for reporting structures, and single-pass conditional aggregations.

Part 1: Monthly-Reset Cumulative Sales

You are given a list of sales records. Each record is a tuple `(date, store_id, amount)`. Multiple records may exist for the same `store_id` on the same `date`. First, combine records that have the same `store_id` and `date` by summing their amounts. Then, for each store, compute a running total ordered by date. The running total must reset to 0 at the start of each new calendar month. Return the result as a list of tuples `(store_id, date, total_amount)` sorted by `store_id` ascending, then `date` ascending.

Constraints

0 <= len(sales) <= 200000
`date` is always a valid ISO date string in `YYYY-MM-DD` format
1 <= store_id <= 10^9
0 <= amount <= 10^9
Input may contain multiple rows with the same `(store_id, date)`

Examples

Input: [('2023-01-01', 1, 100), ('2023-01-01', 1, 50), ('2023-01-02', 1, 25), ('2023-02-01', 1, 10), ('2023-01-01', 2, 80), ('2023-01-03', 2, 20), ('2023-02-02', 2, 5)]

Expected Output: [(1, '2023-01-01', 150), (1, '2023-01-02', 175), (1, '2023-02-01', 10), (2, '2023-01-01', 80), (2, '2023-01-03', 100), (2, '2023-02-02', 5)]

Explanation: Duplicate rows for store 1 on 2023-01-01 are merged first. Running totals reset in February for each store.

Input: [('2023-03-05', 1, 40), ('2023-02-28', 1, 60), ('2023-03-01', 1, 10)]

Expected Output: [(1, '2023-02-28', 60), (1, '2023-03-01', 10), (1, '2023-03-05', 50)]

Explanation: Input is unsorted. The March running total starts over from 0.

Input: []

Expected Output: []

Explanation: Edge case: no sales records.

Input: [('2024-12-31', 3, 7)]

Expected Output: [(3, '2024-12-31', 7)]

Explanation: Edge case: a single aggregated row remains unchanged.

Hints

Aggregate duplicate `(store_id, date)` rows before computing any running total.
Because dates are in `YYYY-MM-DD` format, lexicographic sorting matches chronological order.

Part 2: Full Reporting Hierarchy

You are given an organization chart as a list of tuples `(employee_id, manager_id, name)`. Find every employee who reports to the manager with ID `1`, including both direct and indirect reports. For each employee under manager `1`, return: - `employee_id` - `manager_id` - `name` - `level`: distance from manager `1` (`1` for direct reports, `2` for reports of reports, and so on) - `hierarchy_path`: a string showing the chain of employee IDs from manager `1` to that employee, joined by `->` Exclude employee `1` from the output. Return the final list sorted by `level` ascending, then `employee_id` ascending.

Constraints

0 <= len(organization) <= 200000
Employee IDs are unique
Each employee has at most one manager
The management structure contains no cycles
If employee `1` does not exist, return an empty list

Examples

Input: [(1, None, 'CEO'), (2, 1, 'Alice'), (3, 1, 'Bob'), (4, 2, 'Cara'), (5, 2, 'Dan'), (6, 4, 'Eve'), (7, 3, 'Finn')]

Expected Output: [(2, 1, 'Alice', 1, '1->2'), (3, 1, 'Bob', 1, '1->3'), (4, 2, 'Cara', 2, '1->2->4'), (5, 2, 'Dan', 2, '1->2->5'), (7, 3, 'Finn', 2, '1->3->7'), (6, 4, 'Eve', 3, '1->2->4->6')]

Explanation: Employees 2 and 3 report directly to 1. The rest are found by traversing deeper levels.

Input: [(2, 1, 'Alice'), (3, 2, 'Bob')]

Expected Output: []

Explanation: Edge case: employee 1 is missing, so no hierarchy rooted at 1 can be formed.

Input: [(1, None, 'CEO')]

Expected Output: []

Explanation: Edge case: employee 1 exists but has no reports.

Input: [(4, 2, 'Dev'), (2, 1, 'VP'), (1, None, 'CEO'), (3, None, 'OtherRoot'), (5, 3, 'X')]

Expected Output: [(2, 1, 'VP', 1, '1->2'), (4, 2, 'Dev', 2, '1->2->4')]

Explanation: Only employees connected under manager 1 are included; the separate tree rooted at employee 3 is ignored.

Hints

Build a graph from each manager to their direct reports.
A BFS or DFS can carry both the current level and the path built so far.

Part 3: Conditional Order Metrics by Date

You are given a list of order records. Each record is a tuple `(order_id, status, amount, date)`, where `status` is one of `pending`, `completed`, or `cancelled`. Compute the following metrics for each date: - `total_revenue`: sum of `amount` for completed orders only - `pending_order_count`: number of pending orders - `cancelled_order_count`: number of cancelled orders Return the result as a list of tuples `(date, total_revenue, pending_order_count, cancelled_order_count)` sorted by date ascending.

Constraints

0 <= len(orders) <= 200000
`status` is always one of `pending`, `completed`, or `cancelled`
0 <= amount <= 10^9
`date` is always a valid ISO date string in `YYYY-MM-DD` format

Examples

Input: [(101, 'completed', 100, '2023-01-01'), (102, 'pending', 50, '2023-01-01'), (103, 'cancelled', 70, '2023-01-01'), (104, 'completed', 30, '2023-01-02'), (105, 'pending', 20, '2023-01-02'), (106, 'completed', 10, '2023-01-01')]

Expected Output: [('2023-01-01', 110, 1, 1), ('2023-01-02', 30, 1, 0)]

Explanation: Completed amounts are summed into revenue; pending and cancelled orders are counted separately.

Input: [(1, 'pending', 5, '2023-03-02'), (2, 'cancelled', 7, '2023-03-01'), (3, 'pending', 4, '2023-03-01')]

Expected Output: [('2023-03-01', 0, 1, 1), ('2023-03-02', 0, 1, 0)]

Explanation: Dates with no completed orders still appear with revenue 0.

Input: []

Expected Output: []

Explanation: Edge case: no orders.

Input: [(999, 'completed', 999, '2024-12-31')]

Expected Output: [('2024-12-31', 999, 0, 0)]

Explanation: Edge case: one completed order on a single day.

Hints

For each date, keep three running metrics in the same dictionary entry.
You do not need three separate passes over the input.

Quick Overview

This question evaluates proficiency in SQL data transformation and aggregation, testing competencies such as monthly-reset running totals, hierarchical recursion for reporting structures, and single-pass conditional aggregations.