Sign in to track progress, unlock prep layers, and browse your pipeline.Sign in
Write SQL revenue and anomaly queries
Problem Description
You are provided with two tables: transactions and vendors.
transactions Table
This table records individual financial transactions.
| Column | Type | Description |
|---|---|---|
transaction_id | int | Unique identifier for each transaction. |
vendor_id | int | Identifier of the vendor involved in the transaction. |
amount_charged | int | The monetary amount associated with the transaction. |
transaction_type | varchar | Indicates whether the transaction is a PURCHASE or a REFUND. |
refund_transaction_id | int | If the transaction_type is REFUND, this column stores the transaction_id of the original purchase being refunded. Otherwise, it is NULL. |
timestamp | datetime | The exact time when the transaction occurred. |
Sample Rows:
transaction_id | vendor_id | amount_charged | transaction_type | refund_transaction_id | timestamp |
|---|---|---|---|---|---|
| 101 | 1 | 200 | PURCHASE | null | 2025-01-01 |
| 102 | 1 | 150 | PURCHASE | null | 2025-01-10 |
| 103 | 1 | 200 | REFUND | 101 | 2025-01-15 |
| 104 | 2 | 300 | PURCHASE | null | 2025-02-01 |
| 105 | 3 | 500 | PURCHASE | null | 2025-03-01 |
vendors Table
This table contains geographical information about each vendor.
| Column | Type | Description |
|---|---|---|
vendor_id | int | Unique identifier for each vendor. |
city | varchar | The city where the vendor is located. |
state | varchar | The state where the vendor is located. |
country | varchar | The country where the vendor is located. |
Sample Rows:
vendor_id | city | state | country |
|---|---|---|---|
| 1 | San Jose | CA | US |
| 2 | San Jose | CA | US |
| 3 | Austin | TX | US |
| 4 | Seattle | WA | US |
| 5 | Dallas | TX | US |
General Assumptions
- All time-based calculations (e.g., "past two years") should be relative to the current date.
- For revenue calculations,
PURCHASEtransactions should be treated as positive revenue, andREFUNDtransactions as negative revenue. This convention applies unless a question specifically requests the gross charged amount.
Questions
- Find the top 3 vendors with the highest total net amount charged over the past 2 years.
- For each state and city combination, find the top 3 vendors with the highest total net amount charged over the past 2 years.
- Propose several data quality or anomaly checks for this transaction data, and write SQL queries to detect those issues. Consider issues such as:
- Invalid refund references (e.g.,
refund_transaction_idpoints to a non-existent transaction). - Refund amounts that do not match the original purchase amount.
- Duplicate
transaction_idvalues. - Transactions with
vendor_ids not present in thevendorstable. - Unusual spikes in transaction volume or amount.
- Invalid refund references (e.g.,
AI interview prep
Core dilemma
Balancing the complexity of SQL logic for accurate business metric aggregation (net revenue, time windows, top-N per group) with query performance and robust data quality validation. The challenge lies in writing efficient, correct, and maintainable SQL that handles various edge cases and data anomalies.
Mid-level signals3
Senior signals3
Red flags3
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Write SQL revenue and anomaly queries
Problem Description
You are provided with two tables: transactions and vendors.
transactions Table
This table records individual financial transactions.
| Column | Type | Description |
|---|---|---|
transaction_id | int | Unique identifier for each transaction. |
vendor_id | int | Identifier of the vendor involved in the transaction. |
amount_charged | int | The monetary amount associated with the transaction. |
transaction_type | varchar | Indicates whether the transaction is a PURCHASE or a REFUND. |
refund_transaction_id | int | If the transaction_type is REFUND, this column stores the transaction_id of the original purchase being refunded. Otherwise, it is NULL. |
timestamp | datetime | The exact time when the transaction occurred. |
Sample Rows:
transaction_id | vendor_id | amount_charged | transaction_type | refund_transaction_id | timestamp |
|---|---|---|---|---|---|
| 101 | 1 | 200 | PURCHASE | null | 2025-01-01 |
| 102 | 1 | 150 | PURCHASE | null | 2025-01-10 |
| 103 | 1 | 200 | REFUND | 101 | 2025-01-15 |
| 104 | 2 | 300 | PURCHASE | null | 2025-02-01 |
| 105 | 3 | 500 | PURCHASE | null | 2025-03-01 |
vendors Table
This table contains geographical information about each vendor.
| Column | Type | Description |
|---|---|---|
vendor_id | int | Unique identifier for each vendor. |
city | varchar | The city where the vendor is located. |
state | varchar | The state where the vendor is located. |
country | varchar | The country where the vendor is located. |
Sample Rows:
vendor_id | city | state | country |
|---|---|---|---|
| 1 | San Jose | CA | US |
| 2 | San Jose | CA | US |
| 3 | Austin | TX | US |
| 4 | Seattle | WA | US |
| 5 | Dallas | TX | US |
General Assumptions
- All time-based calculations (e.g., "past two years") should be relative to the current date.
- For revenue calculations,
PURCHASEtransactions should be treated as positive revenue, andREFUNDtransactions as negative revenue. This convention applies unless a question specifically requests the gross charged amount.
Questions
- Find the top 3 vendors with the highest total net amount charged over the past 2 years.
- For each state and city combination, find the top 3 vendors with the highest total net amount charged over the past 2 years.
- Propose several data quality or anomaly checks for this transaction data, and write SQL queries to detect those issues. Consider issues such as:
- Invalid refund references (e.g.,
refund_transaction_idpoints to a non-existent transaction). - Refund amounts that do not match the original purchase amount.
- Duplicate
transaction_idvalues. - Transactions with
vendor_ids not present in thevendorstable. - Unusual spikes in transaction volume or amount.
- Invalid refund references (e.g.,