Quick Overview

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.

Compute per-coin quarterly amounts and totals

Company: Others

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: easy

Interview Round: Online Assessment

## 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.

Overview: 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.

Read the full Others Data Scientist interview experience this question came from

You are given two tables: `coins(coin_id, name)` and `transactions(id, coin_id, dt, amount)`. Write a SQL query that returns one row per coin with the following columns: - `name` - `q1_amount`: total transaction amount in Q1 (Jan–Mar) - `q2_amount`: total transaction amount in Q2 (Apr–Jun) - `q3_amount`: total transaction amount in Q3 (Jul–Sep) - `q4_amount`: total transaction amount in Q4 (Oct–Dec) - `total_transactions`: total number of transactions for the coin - `total_amount`: total transaction amount for the coin If a coin has no transactions in a quarter, return 0 for that quarter. Use calendar quarters based on `transactions.dt` (assume all sample data is within the same year).

Tables

coins(coin_id INT, name VARCHAR(50))

transactions(id INT, coin_id INT, dt DATE, amount DECIMAL(12,2))

Hints

  1. Use conditional aggregation: SUM(CASE WHEN ... THEN amount END) for each quarter.
  2. LEFT JOIN from coins to transactions so coins with no transactions still appear (use COALESCE to return 0).

Loading coding console...