2,170 answered market questions

WITH vendor AS
(
    SELECT
        ticker,
        argMax(toFloat64(market_cap), date)             AS mkt_cap,
        argMax(toFloat64(price), date)                  AS px,
        argMax(formatDateTime(date, '%b %e, %Y'), date) AS priced_on
    FROM global_markets.stocks_ratios
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'JPM', 'XOM')
      AND price > 0
      AND market_cap > 0
    GROUP BY ticker
),
filed AS
(
    SELECT
        ticker,
        argMax(toFloat64(diluted_shares_outstanding), (filing_date, period_end))   AS diluted,
        argMax(formatDateTime(period_end, '%b %e, %Y'), (filing_date, period_end)) AS period_covered
    FROM
    (
        SELECT
            arrayJoin(tickers) AS ticker,
            filing_date,
            period_end,
            diluted_shares_outstanding
        FROM global_markets.stocks_income_statements
        WHERE hasAny(tickers, ['AAPL', 'MSFT', 'NVDA', 'KO', 'JPM', 'XOM'])
          AND timeframe = 'quarterly'
          AND diluted_shares_outstanding > 0
    )
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'JPM', 'XOM')
    GROUP BY ticker
)
SELECT
    v.ticker                                                      AS ticker,
    round(v.mkt_cap / v.px / 1e6)                                 AS implied_shares_mm,
    round(f.diluted / 1e6)                                        AS filed_diluted_shares_mm,
    round(abs(v.mkt_cap / v.px - f.diluted) * 100 / f.diluted, 2) AS gap_pct,
    f.period_covered                                              AS period_covered,
    v.priced_on                                                   AS priced_on
FROM vendor AS v
INNER JOIN filed AS f ON f.ticker = v.ticker
ORDER BY gap_pct DESC