Count, Return, Find, and Select in SQL Queries
Company: OneMain Financial
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
orders
+----------+--------------+------------+--------+
| order_id | customer_id | order_date | amount |
+----------+--------------+------------+--------+
| 1 | 101 | 2024-01-05 | 250.00 |
| 2 | 102 | 2024-01-07 | 125.50 |
| 3 | 101 | 2024-01-10 | 80.00 |
| 4 | 103 | 2024-01-11 | 300.00 |
| 5 | 104 | 2024-01-15 | 150.00 |
+----------+--------------+------------+--------+
##### Scenario
SQL screening – answer four basic queries on a single orders table
##### Question
Count how many orders each customer has made. Return the total revenue generated per day. Find the customer(s) with the highest single order amount. Select all orders whose amount is above the overall average.
##### Hints
GROUP BY, ORDER BY, HAVING and window functions might help.
Overview: This question evaluates proficiency in SQL data manipulation, specifically aggregation, grouping, filtering, and identification of extrema within a single-table orders dataset.
Orders per customer count
For each customer, count how many orders they have placed.
Tables
orders(order_id INTEGER, customer_id INTEGER, order_date DATE, amount DECIMAL(10,2))
Hints
- Group rows by customer_id.
- Use COUNT(*) to count orders.
Daily total revenue
Calculate the total revenue generated for each order_date.
Tables
orders(order_id INTEGER, customer_id INTEGER, order_date DATE, amount DECIMAL(10,2))
Hints
- Aggregate with SUM(amount).
- Group by order_date.
Highest single order amount
Find the order(s) with the highest single order amount and return order_id, customer_id, and amount.
Tables
orders(order_id INTEGER, customer_id INTEGER, order_date DATE, amount DECIMAL(10,2))
Hints
- Find MAX(amount) first.
- Filter orders where amount equals that maximum.
Orders above average amount
Select all orders whose amount is greater than the overall average order amount.
Tables
orders(order_id INTEGER, customer_id INTEGER, order_date DATE, amount DECIMAL(10,2))
Hints
- Compute the overall AVG(amount) in a subquery.
- Filter orders with amount greater than that average.