Cos'è un'IPO e come funziona la quotazione in Borsa

WITH
    offerte AS
    (
        SELECT
            ticker,
            argMax(toDate(listing_date), _ingest_time)           AS data_quotazione,
            argMax(toFloat64(final_issue_price), _ingest_time)   AS prezzo_offerta,
            argMax(toFloat64(lowest_offer_price), _ingest_time)  AS forchetta_min,
            argMax(toFloat64(highest_offer_price), _ingest_time) AS forchetta_max
        FROM global_markets.stocks_ipos
        WHERE toDate(listing_date) >= '2026-01-01'
          AND toDate(listing_date) <  '2026-07-01'
          AND lower(ifNull(ipo_status, '')) NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
          AND final_issue_price >= 4
          AND lowest_offer_price > 0
          AND highest_offer_price >= lowest_offer_price
          AND lower(ifNull(issuer_name, '')) NOT LIKE '%acquisition%'
          AND lower(ifNull(security_description, '')) NOT LIKE '%unit%'
          AND lower(ifNull(security_description, '')) NOT LIKE '%warrant%'
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
    ),
    prime_sedute AS
    (
        SELECT
            o.ticker                           AS simbolo,
            o.prezzo_offerta                   AS prezzo_coll,
            o.forchetta_min                    AS range_min,
            o.forchetta_max                    AS range_max,
            argMin(toFloat64(a.close), a.date) AS prima_chiusura
        FROM global_markets.stocks_daily_aggs AS a
        INNER JOIN offerte AS o ON a.ticker = o.ticker
        WHERE a.ticker IN (SELECT ticker FROM offerte)
          AND a.date >= '2026-01-01'
          AND a.date <  '2026-07-10'
          AND a.date >= o.data_quotazione
          AND a.date <= o.data_quotazione + 5
          AND a.volume > 0
        GROUP BY o.ticker, o.prezzo_offerta, o.forchetta_min, o.forchetta_max
    )
SELECT
    multiIf(prezzo_coll > range_max, 'sopra la forchetta',
            prezzo_coll < range_min, 'sotto la forchetta',
                                     'dentro la forchetta')                AS esito_del_bookbuilding,
    count()                                                                AS numero_debutti,
    round(count() / sum(count()) OVER () * 100, 1)                         AS quota_pct,
    round(quantileExact(0.5)(prima_chiusura / prezzo_coll * 100 - 100), 1) AS rendimento_mediano_primo_giorno_pct,
    round(countIf(prima_chiusura < prezzo_coll) / count() * 100, 1)        AS chiusi_sotto_il_prezzo_pct
FROM prime_sedute
GROUP BY esito_del_bookbuilding
ORDER BY multiIf(esito_del_bookbuilding = 'sotto la forchetta', 1,
                 esito_del_bookbuilding = 'dentro la forchetta', 2, 3)

WITH
    offerte AS
    (
        SELECT
            ticker,
            argMax(toDate(listing_date), _ingest_time)         AS data_quotazione,
            argMax(toFloat64(final_issue_price), _ingest_time) AS prezzo_offerta
        FROM global_markets.stocks_ipos
        WHERE toDate(listing_date) >= '2025-07-01'
          AND toDate(listing_date) <  '2026-07-01'
          AND lower(ifNull(ipo_status, '')) NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
          AND final_issue_price >= 4
          AND lower(ifNull(issuer_name, '')) NOT LIKE '%acquisition%'
          AND lower(ifNull(security_description, '')) NOT LIKE '%unit%'
          AND lower(ifNull(security_description, '')) NOT LIKE '%warrant%'
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
    ),
    prime_sedute AS
    (
        SELECT
            o.ticker                           AS simbolo,
            o.data_quotazione                  AS quotato_il,
            o.prezzo_offerta                   AS prezzo_coll,
            argMin(toFloat64(a.close), a.date) AS prima_chiusura
        FROM global_markets.stocks_daily_aggs AS a
        INNER JOIN offerte AS o ON a.ticker = o.ticker
        WHERE a.ticker IN (SELECT ticker FROM offerte)
          AND a.date >= '2025-07-01'
          AND a.date <  '2026-07-10'
          AND a.date >= o.data_quotazione
          AND a.date <= o.data_quotazione + 5
          AND a.volume > 0
        GROUP BY o.ticker, o.data_quotazione, o.prezzo_offerta
    )
SELECT
    if(quotato_il < toDate('2026-01-01'), 'luglio-dicembre 2025', 'gennaio-giugno 2026') AS semestre,
    count()                                                                AS numero_debutti,
    round(quantileExact(0.5)(prima_chiusura / prezzo_coll * 100 - 100), 1) AS rendimento_mediano_primo_giorno_pct,
    round(countIf(prima_chiusura < prezzo_coll) / count() * 100, 1)        AS chiusi_sotto_il_prezzo_pct,
    round(countIf(prima_chiusura > prezzo_coll) / count() * 100, 1)        AS chiusi_sopra_il_prezzo_pct
FROM prime_sedute
GROUP BY semestre
ORDER BY min(quotato_il)

WITH
    offerte AS
    (
        SELECT
            ticker,
            argMax(toDate(listing_date), _ingest_time)         AS data_quotazione,
            argMax(toFloat64(final_issue_price), _ingest_time) AS prezzo_offerta
        FROM global_markets.stocks_ipos
        WHERE toDate(listing_date) >= '2026-01-01'
          AND toDate(listing_date) <  '2026-07-01'
          AND lower(ifNull(ipo_status, '')) NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
          AND final_issue_price >= 4
          AND lower(ifNull(issuer_name, '')) NOT LIKE '%acquisition%'
          AND lower(ifNull(security_description, '')) NOT LIKE '%unit%'
          AND lower(ifNull(security_description, '')) NOT LIKE '%warrant%'
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
    ),
    prime_sedute AS
    (
        SELECT
            o.ticker                           AS simbolo,
            o.prezzo_offerta                   AS prezzo_coll,
            argMin(toFloat64(a.close), a.date) AS prima_chiusura
        FROM global_markets.stocks_daily_aggs AS a
        INNER JOIN offerte AS o ON a.ticker = o.ticker
        WHERE a.ticker IN (SELECT ticker FROM offerte)
          AND a.date >= '2026-01-01'
          AND a.date <  '2026-07-10'
          AND a.date >= o.data_quotazione
          AND a.date <= o.data_quotazione + 5
          AND a.volume > 0
        GROUP BY o.ticker, o.prezzo_offerta
    )
SELECT
    multiIf(variazione < 0,  'chiusura sotto il prezzo di collocamento',
            variazione < 10, 'da 0% a +10%',
            variazione < 25, 'da +10% a +25%',
            variazione < 50, 'da +25% a +50%',
                             'oltre +50%')             AS fascia_di_rendimento_primo_giorno,
    count()                                            AS numero_debutti,
    round(count() / sum(count()) OVER () * 100, 1)     AS quota_pct
FROM
(
    SELECT prima_chiusura / prezzo_coll * 100 - 100 AS variazione
    FROM prime_sedute
)
GROUP BY fascia_di_rendimento_primo_giorno
ORDER BY min(variazione)

WITH
    offerte AS
    (
        SELECT
            ticker,
            argMax(toDate(listing_date), _ingest_time)         AS data_quotazione,
            argMax(toFloat64(final_issue_price), _ingest_time) AS prezzo_offerta,
            argMax(toFloat64(total_offer_size), _ingest_time)  AS raccolta,
            argMax(issuer_name, _ingest_time)                  AS emittente
        FROM global_markets.stocks_ipos
        WHERE toDate(listing_date) >= '2026-01-01'
          AND toDate(listing_date) <  '2026-07-01'
          AND lower(ifNull(ipo_status, '')) NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
          AND final_issue_price >= 4
          AND total_offer_size > 0
          AND lower(ifNull(issuer_name, '')) NOT LIKE '%acquisition%'
          AND lower(ifNull(security_description, '')) NOT LIKE '%unit%'
          AND lower(ifNull(security_description, '')) NOT LIKE '%warrant%'
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
    ),
    prime_sedute AS
    (
        SELECT
            o.ticker                           AS simbolo,
            o.emittente                        AS societa_emittente,
            o.data_quotazione                  AS quotato_il,
            o.prezzo_offerta                   AS prezzo_coll,
            o.raccolta                         AS raccolta_usd,
            argMin(toFloat64(a.open),  a.date) AS prima_apertura_usd,
            argMin(toFloat64(a.close), a.date) AS prima_chiusura_usd
        FROM global_markets.stocks_daily_aggs AS a
        INNER JOIN offerte AS o ON a.ticker = o.ticker
        WHERE a.ticker IN (SELECT ticker FROM offerte)
          AND a.date >= '2026-01-01'
          AND a.date <  '2026-07-10'
          AND a.date >= o.data_quotazione
          AND a.date <= o.data_quotazione + 5
          AND a.volume > 0
        GROUP BY o.ticker, o.emittente, o.data_quotazione, o.prezzo_offerta, o.raccolta
    )
SELECT
    simbolo                                                AS ticker,
    substring(societa_emittente, 1, 40)                    AS societa,
    formatDateTime(quotato_il, '%d/%m/%Y')                 AS primo_giorno_di_negoziazione,
    concat('$', toString(round(prezzo_coll, 2)))           AS prezzo_di_collocamento,
    concat('$', toString(round(prima_apertura_usd, 2)))    AS prima_apertura,
    concat('$', toString(round(prima_chiusura_usd, 2)))    AS prima_chiusura_ufficiale,
    round(prima_chiusura_usd / prezzo_coll * 100 - 100, 1) AS rendimento_primo_giorno_pct,
    toUInt32(round(raccolta_usd / 1e6))                    AS raccolta_mln_usd
FROM prime_sedute
ORDER BY raccolta_usd DESC
LIMIT 12