今後90日以内のIPOロックアップ解除予定日

SELECT
    ticker,
    company,
    toString(listed_d)                                          AS listed_on,
    concat(formatDateTime(listed_d, '%b'), ' ',
           toString(toDayOfMonth(listed_d)))                    AS listed_label,
    toString(unlock_d)                                          AS unlock_180d,
    concat(formatDateTime(unlock_d, '%a, %b'), ' ',
           toString(toDayOfMonth(unlock_d)))                    AS unlock_label,
    dateDiff('day', today(), unlock_d)                          AS days_away,
    concat(monthName(today()), ' ',
           toString(toDayOfMonth(today())), ', ',
           toString(toYear(today())))                           AS computed_on
FROM
(
    SELECT
        ticker,
        argMax(issuer_name, _ingest_time)                       AS company,
        argMax(toDate(listing_date), _ingest_time)              AS listed_d,
        addDays(listed_d, 180)                                  AS unlock_d
    FROM global_markets.stocks_ipos
    WHERE ticker != ''
      AND ticker NOT IN ('SPCX')
      AND toDate(listing_date) BETWEEN today() - 180 AND today() - 90
      AND ipo_status NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
      AND positionCaseInsensitive(issuer_name, 'acquisition') = 0
      AND final_issue_price > 0
    GROUP BY ticker
)
ORDER BY unlock_d, ticker

SELECT
    i.ticker                                                AS ticker,
    round(i.offer_px, 2)                                    AS offer_price,
    round(p.last_px, 2)                                     AS last_close,
    round((p.last_px / i.offer_px - 1) * 100, 1)            AS vs_offer_pct,
    concat(monthName(p.last_d), ' ',
           toString(toDayOfMonth(p.last_d)), ', ',
           toString(toYear(p.last_d)))                      AS priced_as_of
FROM
(
    SELECT
        ticker,
        argMax(toFloat64(final_issue_price), _ingest_time)  AS offer_px
    FROM global_markets.stocks_ipos
    WHERE ticker != ''
      AND ticker NOT IN ('SPCX')
      AND toDate(listing_date) BETWEEN today() - 180 AND today() - 90
      AND ipo_status NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
      AND positionCaseInsensitive(issuer_name, 'acquisition') = 0
      AND final_issue_price > 0
    GROUP BY ticker
) AS i
INNER JOIN
(
    SELECT
        ticker,
        toFloat64(argMax(close, date))                      AS last_px,
        max(date)                                           AS last_d
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 10
      AND close > 0
    GROUP BY ticker
) AS p ON p.ticker = i.ticker
ORDER BY vs_offer_pct DESC, ticker

SELECT
    formatDateTime(listing_month, '%Y-%m')                  AS month,
    formatDateTime(listing_month, '%b %Y')                  AS month_label,
    count()                                                 AS ipo_count
FROM
(
    SELECT
        ticker,
        toStartOfMonth(argMax(toDate(listing_date), _ingest_time)) AS listing_month
    FROM global_markets.stocks_ipos
    WHERE ticker != ''
      AND ticker NOT IN ('SPCX')
      AND toDate(listing_date) BETWEEN today() - 365 AND today()
      AND ipo_status NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
      AND positionCaseInsensitive(issuer_name, 'acquisition') = 0
      AND final_issue_price > 0
    GROUP BY ticker
)
GROUP BY listing_month
ORDER BY listing_month

SELECT
    k.ticker                                                   AS ticker,
    toString(toDate(k.filing_date))                            AS filed_on,
    concat(formatDateTime(toDate(k.filing_date), '%b'), ' ',
           toString(toDayOfMonth(toDate(k.filing_date))))      AS filed_label,
    any(k.form_type)                                           AS form_type,
    max(countSubstringsCaseInsensitive(k.items_text, 'lock-up')
        + countSubstringsCaseInsensitive(k.items_text, 'lockup')) AS lockup_mentions,
    any(k.filing_url)                                          AS filing_url
FROM global_markets.stocks_8k_text AS k
WHERE k.ticker IN
(
    SELECT ticker
    FROM global_markets.stocks_ipos
    WHERE ticker != ''
      AND ticker NOT IN ('SPCX')
      AND toDate(listing_date) BETWEEN today() - 730 AND today() - 30
      AND ipo_status NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
      AND positionCaseInsensitive(issuer_name, 'acquisition') = 0
      AND final_issue_price > 0
)
  AND toDate(k.filing_date) >= today() - 180
  AND (positionCaseInsensitive(k.items_text, 'lock-up') > 0
       OR positionCaseInsensitive(k.items_text, 'lockup') > 0)
GROUP BY k.ticker, k.accession_number, k.filing_date
ORDER BY k.filing_date DESC, k.ticker
LIMIT 25