טסלה 10-K ביטקוין: 8 בפברואר 2021 בטייפ

WITH (
    SELECT round(toFloat64(cash_and_equivalents) / 1e9, 1)
    FROM global_markets.stocks_balance_sheets
    WHERE has(tickers, 'TSLA') AND timeframe = 'annual' AND fiscal_year = 2020
    LIMIT 1
) AS yearend_cash_bn
SELECT
    count() AS tesla_filings_that_day,
    countIf(form_type = '10-K') AS annual_reports,
    any(form_type) AS form_type_on_file,
    yearend_cash_bn AS fy2020_cash_bn,
    round(100 * 1.5 / yearend_cash_bn, 1) AS purchase_pct_of_cash
FROM global_markets.stocks_sec_edgar_index
WHERE filing_date = '2021-02-08'
  AND (issuer_name ILIKE '%tesla%' OR cik = '0001318605')

WITH
    (
        SELECT argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'TSLA'
          AND window_start >= toDateTime('2021-02-05 00:00:00') AND window_start < toDateTime('2021-02-08 04:00:00')
    ) AS prior_rth_close
SELECT
    round(prior_rth_close, 2) AS prior_close,
    round(toFloat64(argMinIf(open, window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)), 2) AS rth_open,
    round((toFloat64(argMinIf(open, window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)) / prior_rth_close - 1) * 100, 1) AS gap_pct,
    round(minIf(toFloat64(low), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 2) AS rth_low,
    formatDateTime(toTimeZone(argMinIf(window_start, (toFloat64(low), toInt64(toUnixTimestamp(window_start))), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 'America/New_York'), '%H:%i') AS low_et,
    round(maxIf(toFloat64(high), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 2) AS rth_high,
    round(toFloat64(argMaxIf(close, window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)), 2) AS rth_close,
    round((toFloat64(argMaxIf(close, window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)) / prior_rth_close - 1) * 100, 1) AS day_change_pct,
    round((minIf(toFloat64(low), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) / prior_rth_close - 1) * 100, 1) AS low_vs_prior_pct,
    round(toFloat64(sum(volume)) / 1e6, 1) AS day_shares_m,
    countIf((toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS rth_minute_bars
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'TSLA'
  AND window_start >= toDateTime('2021-02-08 04:00:00') AND window_start < toDateTime('2021-02-08 23:59:00')

WITH daily AS (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS close_usd
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'TSLA'
      AND window_start >= toDateTime('2020-08-31 00:00:00') AND window_start < toDateTime('2021-02-09 01:00:00')
    GROUP BY et_date
),
chg AS (
    SELECT
        et_date,
        close_usd,
        lagInFrame(close_usd) OVER (ORDER BY et_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM daily
)
SELECT
    countIf(et_date >= toDate('2020-09-01') AND et_date < toDate('2021-02-08') AND prev_close > 0) AS baseline_sessions,
    round(quantileDeterministicIf(0.5)(abs(close_usd / prev_close - 1) * 100, toUInt32(et_date), et_date >= toDate('2020-09-01') AND et_date < toDate('2021-02-08') AND prev_close > 0), 1) AS median_abs_move_pct,
    countIf(et_date >= toDate('2020-09-01') AND et_date < toDate('2021-02-08') AND prev_close > 0 AND abs(close_usd / prev_close - 1) > abs((SELECT close_usd / prev_close - 1 FROM chg WHERE et_date = toDate('2021-02-08')))) AS sessions_moving_more,
    round(anyIf((close_usd / prev_close - 1) * 100, et_date = toDate('2021-02-08')), 1) AS feb8_change_pct
FROM chg
HAVING countIf(et_date >= toDate('2020-09-01') AND et_date < toDate('2021-02-08') AND prev_close > 0) > 0

SELECT
    formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
    round(toFloat64(argMax(close, window_start)), 2) AS bucket_close,
    round(min(toFloat64(low)), 2) AS bucket_low,
    round(toFloat64(sum(volume)) / 1e6, 1) AS shares_m
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'TSLA'
  AND window_start >= toDateTime('2021-02-08 04:00:00') AND window_start < toDateTime('2021-02-08 23:59:00')
  AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
GROUP BY et_time
ORDER BY et_time

SELECT
    ticker,
    round((event_close / prior_close - 1) * 100, 1) AS change_pct
FROM (
    SELECT
        ticker,
        argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2021-02-05') AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS prior_close,
        argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2021-02-08') AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS event_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('MSTR', 'RIOT', 'MARA', 'SQ', 'PYPL', 'SPY')
      AND window_start >= toDateTime('2021-02-05 00:00:00') AND window_start < toDateTime('2021-02-09 01:00:00')
    GROUP BY ticker
)
ORDER BY change_pct DESC

WITH daily AS (
    SELECT
        toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session,
        toFloat64(sum(size)) AS contracts,
        toFloat64(sumIf(size, option_type = 'P')) AS put_contracts,
        toFloat64(sumIf(size, option_type = 'C')) AS call_contracts
    FROM global_markets.options_trades
    WHERE ticker LIKE 'O:TSLA2%'
      AND sip_timestamp >= toDateTime('2021-01-08 00:00:00') AND sip_timestamp < toDateTime('2021-02-09 01:00:00')
    GROUP BY session
)
SELECT
    round(maxIf(contracts, session = toDate('2021-02-08')) / 1e6, 2) AS event_contracts_m,
    round(avgIf(contracts, session < toDate('2021-02-08')) / 1e6, 2) AS prior_avg_contracts_m,
    round(maxIf(contracts, session = toDate('2021-02-08')) / avgIf(contracts, session < toDate('2021-02-08')), 2) AS volume_multiple,
    countIf(session < toDate('2021-02-08')) AS baseline_sessions,
    round(maxIf(put_contracts, session = toDate('2021-02-08')) / maxIf(call_contracts, session = toDate('2021-02-08')), 2) AS event_put_call,
    round(sumIf(put_contracts, session < toDate('2021-02-08')) / sumIf(call_contracts, session < toDate('2021-02-08')), 2) AS baseline_put_call,
    round(sumIf(put_contracts, session < toDate('2021-02-08')) / sumIf(call_contracts, session < toDate('2021-02-08')) - maxIf(put_contracts, session = toDate('2021-02-08')) / maxIf(call_contracts, session = toDate('2021-02-08')), 2) AS callward_shift
FROM daily
HAVING countIf(session = toDate('2021-02-08')) > 0 AND countIf(session < toDate('2021-02-08')) > 0

WITH daily AS (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        round(argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 2) AS close_usd
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'TSLA'
      AND window_start >= toDateTime('2021-02-08 00:00:00') AND window_start < toDateTime('2021-03-11 01:00:00')
    GROUP BY et_date
)
SELECT
    toString(et_date) AS session,
    close_usd,
    round((close_usd / (SELECT close_usd FROM daily WHERE et_date = toDate('2021-02-08')) - 1) * 100, 1) AS pct_from_feb8
FROM daily
ORDER BY et_date ASC
LIMIT 21

WITH
    (
        SELECT round(argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 2)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'TSLA'
          AND window_start >= toDateTime('2022-07-20 00:00:00') AND window_start < toDateTime('2022-07-21 04:00:00')
    ) AS jul20_close,
    (
        SELECT count()
        FROM global_markets.stocks_sec_edgar_index
        WHERE cik = '0001318605' AND filing_date = '2022-07-20' AND form_type = '8-K'
    ) AS eightk_count
SELECT
    eightk_count AS disclosure_8k_on_file,
    jul20_close AS prior_close,
    round(argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 2) AS jul21_close,
    round((argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) / jul20_close - 1) * 100, 1) AS next_session_change_pct,
    round(toFloat64(sum(volume)) / 1e6, 1) AS jul21_shares_m
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'TSLA'
  AND window_start >= toDateTime('2022-07-21 00:00:00') AND window_start < toDateTime('2022-07-21 23:59:00')