Nvidia's market-data team wants to compare the true average trading price of several securities during regular market hours on March 14, 2025. For each ticker, calculate the time-weighted average price from 09:30:00 through 16:00:00. A price remains active until the next valid price update. Updates before the market opens should provide the opening price when no newer update exists, while updates after the market closes must not affect the result. If multiple updates occur at the same timestamp, use the update with the greater update ID as the final price at that timestamp. Ignore updates with missing prices. Return the ticker, trading date, time-weighted average price rounded to four decimal places, and the number of covered trading seconds. Sort by time-weighted average price descending. If two tickers have the same value, sort by ticker descending. ### Table Schema | Table | Column | Type | |---|---|---| | market_sessions | trading_date | date | | market_sessions | session_open | timestamp | | market_sessions | session_close | timestamp | | quote_updates | update_id | integer | | quote_updates | ticker | varchar(10) | | quote_updates | observed_at | timestamp | | quote_updates | price | decimal(12,4) | ### Example Input: market_sessions | trading_date | session_open | session_close | |---|---|---| | 2025-03-14 | 2025-03-14 09:30:00 | 2025-03-14 16:00:00 | ### Example Input: quote_updates | update_id | ticker | observed_at | price | |---:|---|---|---:| | 1 | NVDA | 2025-03-14 09:00:00 | 100.0000 | | 2 | NVDA | 2025-03-14 09:30:00 | 101.0000 | | 3 | NVDA | 2025-03-14 10:00:00 | 102.0000 | | 4 | NVDA | 2025-03-14 12:00:00 | 98.0000 | | 5 | NVDA | 2025-03-14 15:00:00 | 100.0000 | | 6 | NVDA | 2025-03-14 16:30:00 | 999.0000 | | 7 | AMD | 2025-03-14 09:10:00 | 50.0000 | | 8 | AMD | 2025-03-14 09:30:00 | 51.0000 | | 9 | AMD | 2025-03-14 11:00:00 | 49.0000 | | 10 | AMD | 2025-03-14 13:30:00 | 52.0000 | | 11 | AMD | 2025-03-14 15:45:00 | 50.0000 | ### Example Output Explanation NVDA's price contributes 101.0000 for 1,800 seconds, 102.0000 for 7,200 seconds, 98.0000 for 10,800 seconds, and 100.0000 for 3,600 seconds. The total is divided by the 23,400 seconds in the regular trading session. AMD is calculated using the same duration-weighted approach, with its pre-market price clipped at the market open.
DROP TABLE IF EXISTS quote_updates; DROP TABLE IF EXISTS market_sessions; CREATE TABLE market_sessions ( trading_date DATE, session_open TIMESTAMP, session_close TIMESTAMP ); CREATE TABLE quote_updates ( update_id INT, ticker VARCHAR(10), observed_at TIMESTAMP, price DECIMAL(12,4) ); INSERT INTO market_sessions VALUES ('2025-03-14', '2025-03-14 09:30:00', '2025-03-14 16:00:00'); INSERT INTO quote_updates VALUES (1, 'NVDA', '2025-03-14 09:00:00', 100.0000), (2, 'NVDA', '2025-03-14 09:30:00', 101.0000), (3, 'NVDA', '2025-03-14 10:00:00', 102.0000), (4, 'NVDA', '2025-03-14 12:00:00', 98.0000), (5, 'NVDA', '2025-03-14 15:00:00', 100.0000), (6, 'NVDA', '2025-03-14 16:30:00', 999.0000), (7, 'AMD', '2025-03-14 09:10:00', 50.0000), (8, 'AMD', '2025-03-14 09:30:00', 51.0000), (9, 'AMD', '2025-03-14 11:00:00', 49.0000), (10, 'AMD', '2025-03-14 13:30:00', 52.0000), (11, 'AMD', '2025-03-14 15:45:00', 50.0000);