prospectus_receipts

SELECT
    e.ticker                                   AS ticker,
    any(e.form_type)                           AS form_type,
    toString(toDate(e.filing_date))            AS filed_on,
    any(e.filing_url)                          AS prospectus_url
FROM global_markets.stocks_sec_edgar_index AS e
INNER JOIN
(
    SELECT
        ticker,
        argMax(toDate(listing_date), _ingest_time) AS listed_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
) AS i ON i.ticker = e.ticker
WHERE e.form_type IN ('424B4', '424B1')
  AND toDate(e.filing_date) >= today() - 200
  AND toDate(e.filing_date) BETWEEN addDays(i.listed_d, -14) AND addDays(i.listed_d, 14)
GROUP BY e.ticker, e.accession_number, e.filing_date
ORDER BY e.ticker, e.filing_date