The Magnificent 7 Race: Who Is Winning This Year?

How it works — every step, readable

1. 01Daily prices — the Magnificent 7

   filters to the relevant rows · sorts the output

   SELECT ticker, CAST(date AS DATE) AS date, CAST(adj_close AS DOUBLE) AS adj_close FROM read_csv_auto('STOOQ_AAPL_MSFT_NVDA_GOOGL_AMZN_META_TSLA.csv', header=true) WHERE adj_close IS NOT NULL ORDER BY ticker, date

2. 02Year-to-date return, ranked

   combines data from multiple inputs · aggregates rows into summary totals · filters to the relevant rows

   WITH y AS (SELECT date_trunc('year', MAX(date)) AS y0 FROM input_1),
   base AS (SELECT ticker, arg_max(adj_close, date) AS p0 FROM input_1, y WHERE date < y.y0 GROUP BY ticker),
   last AS (SELECT ticker, arg_max(adj_close, date) AS p1 FROM input_1 GROUP BY ticker)
   SELECT CASE ticker WHEN 'AAPL' THEN 'Apple' WHEN 'MSFT' THEN 'Microsoft' WHEN 'NVDA' THEN 'Nvidia' WHEN 'GOOGL' THEN 'Alphabet' WHEN 'AMZN' THEN 'Amazon' WHEN 'META' THEN 'Meta' WHEN 'TSLA' THEN 'Tesla' ELSE ticker END AS company, ROUND(100.0 * (p1 / p0 - 1), 1) AS ytd_return_pct
   FROM base JOIN last USING (ticker) ORDER BY ytd_return_pct DESC

3. 03The punch lines

   buckets values by condition · appends result sets (e.g. a TOTAL row) · filters to the relevant rows

   WITH t AS (SELECT * FROM input_1),
   b AS (SELECT arg_max(company, ytd_return_pct) AS best, max(ytd_return_pct) AS bmax, arg_min(company, ytd_return_pct) AS worst, min(ytd_return_pct) AS wmin,
     avg(ytd_return_pct) AS avg_r, count(*) FILTER (WHERE ytd_return_pct > 0) AS up, count(*) AS n FROM t)
   SELECT area, finding, next_step FROM (
     SELECT 1 AS o, 'VERDICT' AS area, '▲ ' || upper(best) || ' LEADS THE MAGNIFICENT 7 — ' || printf('%+.1f%%', bmax) || ' this year' AS finding, '' AS next_step FROM b
     UNION ALL SELECT 2, 'Leader', best || ' ' || printf('%+.1f%%', bmax), 'The one to beat' FROM b
     UNION ALL SELECT 3, 'Laggard', worst || ' ' || printf('%+.1f%%', wmin), 'Last place so far' FROM b
     UNION ALL SELECT 4, 'The spread', printf('%.0f', bmax - wmin) || ' points between first and last', 'Same year, same "big tech" label' FROM b
     UNION ALL SELECT 5, 'Up this year', CAST(up AS VARCHAR) || ' of ' || CAST(n AS VARCHAR), CASE WHEN up = n THEN 'Every one of them' ELSE CAST(n - up AS VARCHAR) || CASE WHEN n - up = 1 THEN ' is down' ELSE ' are down' END END FROM b
     UNION ALL SELECT 6, '$10,000 split across all seven', ('$' || format('{:,}', CAST(ROUND(10000 * (1 + avg_r / 100)) AS BIGINT))) || ' today', 'Equal weights on Jan 1' FROM b
   ) ORDER BY o