Compute per-coin quarterly amounts and totals
Company: Others
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Take-home Project
## Problem (SQL: Conditional Aggregation)
You have two tables:
### `coins`
- `coin_id` INT PRIMARY KEY
- `name` VARCHAR
### `transactions`
- `id` BIGINT PRIMARY KEY
- `coin_id` INT NOT NULL
- Foreign key referencing `coins.coin_id`
- `dt` DATETIME (assume UTC)
- `amount` DECIMAL(18,2)
Each row in `transactions` is a single transaction for a coin.
### Task
For a given calendar year (use a parameter `:year`), output **one row per coin** with:
- `name`
- `q1_amount`: sum of `amount` for transactions in **Q1** (Jan–Mar) of `:year`
- `q2_amount`: sum of `amount` for **Q2** (Apr–Jun) of `:year`
- `q3_amount`: sum of `amount` for **Q3** (Jul–Sep) of `:year`
- `q4_amount`: sum of `amount` for **Q4** (Oct–Dec) of `:year`
- `total_transactions`: total number of transactions for that coin in `:year`
- `total_amount`: total sum of `amount` for that coin in `:year`
### Notes / Requirements
- Include coins with **no transactions** in `:year` (their amounts/counts should be 0).
- Output columns exactly as listed above.
Quick Answer: This question evaluates competence in SQL data manipulation—specifically time-based conditional aggregation, grouping, joins, and handling entities with no activity—to compute per-coin quarterly sums and year-to-date totals.