Katkat
Back

Compute per-coin quarterly amounts and totals

OthersData Scientist · Online Assessment
EasyData ScientistOnline AssessmentData Manipulation (SQL/Python)

Last updated

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.

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.