This question evaluates proficiency in SQL conditional aggregation and date-based grouping alongside shell scripting skills for recursive filesystem traversal and file size analysis.
You have two tables:
coins(coin_id, name)
transactions(id, coin_id, dt, amount)
Write a query that returns one row per coin with the following columns:
name
q1_amount
,
q2_amount
,
q3_amount
,
q4_amount
(sum of
amount
for transactions whose
dt
falls in quarter 1/2/3/4)
total_transactions
(count of transactions for that coin)
total_amount
(sum of
amount
for that coin)
Assume all transactions are within the same calendar year; if multiple years exist, state how you would filter to a target year.
Given a directory path, implement a shell function that returns the maximum file size (bytes) among all regular files under that directory.
Clarify whether the search is recursive; if not specified, assume recursive (include subdirectories). Handle the empty-directory case sensibly (e.g., return 0 or no output) and avoid counting directories.