Compute per-coin quarterly amounts and totals

Q: Compute per-coin quarterly amounts and totals

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.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Compute per-coin quarterly amounts and totals

Overview

Problem (SQL: Conditional Aggregation)

coins

transactions

Task

Notes / Requirements

Comments (0)

`coins`

`transactions`