3,256 answered market questions

WITH
big_caps AS
(
    SELECT
        ticker,
        argMax(market_cap, date) AS market_cap
    FROM global_markets.stocks_ratios
    WHERE date >= today() - 45
    GROUP BY ticker
    HAVING toFloat64(market_cap) >= 20e9
),
ever_split AS
(
    SELECT DISTINCT ticker
    FROM global_markets.stocks_splits
    WHERE split_to > split_from
)
SELECT
    d.ticker                                                            AS ticker,
    uniqExactIf(d.date, d.close >= 500)                                 AS closes_above_500,
    concat(monthName(minIf(d.date, d.close >= 500)), ' ', toString(toDayOfMonth(minIf(d.date, d.close >= 500))), ', ', toString(toYear(minIf(d.date, d.close >= 500)))) AS first_above_500_in_window,
    concat('$', toString(round(toFloat64(argMax(d.close, d.date)), 2))) AS latest_close,
    concat('$', toString(round(toFloat64(max(d.close)), 2)))            AS highest_close
FROM global_markets.stocks_daily_aggs AS d
WHERE d.date >= today() - 1826
  AND d.ticker IN (SELECT ticker FROM big_caps)
  AND d.ticker NOT IN (SELECT ticker FROM ever_split)
  AND d.ticker NOT IN ('SPCX')
GROUP BY d.ticker
HAVING argMax(d.close, d.date) >= 500
   AND closes_above_500 >= 250
ORDER BY closes_above_500 DESC
LIMIT 12