SQL Challenges · WEEKLY QUACK CHALLENGE!
THE LILY PAD EXCHANGE
Difficulty: medium
Feathers are flying at the Lily Pad Exchange. Director Goldeneye slaps a stack of ticker tape on Duckberts desk. Markets closed three minutes ago and every broker in the building is screaming about who had the big day. Its not who moved the most per share — any fancy-feathered fool can clock a big price swing on a thin float. I want to know which sector actually moved the most money. Total market-cap change. Close minus open, times every share outstanding. Sum it by sector. Bring me the winner…
Your task
Each ticker’s daily price move is its last trade price minus its first; multiply that by its shares outstanding (from `read_parquet(data/files/202635_09_lily_pad/shares_outstanding.parquet)`) to get its market-cap change. Return the sector with the largest total market-cap change; on a tie, the alphabetically first. You will be working with the `trades` and `companies` tables and the shares file.
Tables
trades (21 rows)
| column | type |
|---|---|
ticker | VARCHAR |
ts | TIMESTAMP |
price | DECIMAL(10,2) |
companies (7 rows)
| column | type |
|---|---|
ticker | VARCHAR |
name | VARCHAR |
sector | VARCHAR |
Files
data/files/202635_09_lily_pad/shares_outstanding.parquet (7 rows)
| column | type |
|---|---|
ticker | VARCHAR |
shares | INTEGER |
Database: https://dbquacks.com/data/databases/weekly_202635_09_lily_pad.duckdb
This page as markdown: https://dbquacks.com/challenges/weekly/9.md