How to Get Earnings Call Transcripts into Snowflake (3 Ways)

select symbol, total_transcripts, earliest_fiscal_year, latest_fiscal_year,
       latest_transcript_report_date
from earning_call_transcripts_cnt_vw
where symbol in ('AAPL', 'MSFT', 'NVDA', 'JPM')
order by symbol;

select count(*) as calls, count(distinct symbol) as companies,
       min(report_date) as oldest_call, max(report_date) as newest_call
from earning_call_transcripts_vw
where src_updated_at >= dateadd(day, -7, current_timestamp());

merge into my_transcripts t
using (
    select *
    from earning_call_transcripts_vw
    where src_updated_at >= dateadd(day, -7, current_timestamp())
) s
    on t.symbol = s.symbol
   and t.fiscal_year = s.fiscal_year
   and t.fiscal_quarter = s.fiscal_quarter
when matched then update set
    report_date = s.report_date, transcripts = s.transcripts,
    src_updated_at = s.src_updated_at
when not matched then insert
    (symbol, fiscal_year, fiscal_quarter, report_date, transcripts, src_updated_at)
    values (s.symbol, s.fiscal_year, s.fiscal_quarter, s.report_date,
            s.transcripts, s.src_updated_at);

select count(*) as calls, count(distinct primary_ticker) as companies,
       max(event_timestamp)::date as latest
from (
    select *
    from snowflake_public_data_free.public_data_free.company_event_transcript_attributes
    where event_type = 'Earnings Call'
      and transcript_type = 'SPEAKERS_ANNOTATED'
    qualify row_number() over (partition by primary_ticker, event_timestamp
                               order by updated_at desc) = 1
);

create table if not exists raw_transcripts (
    payload    variant,
    loaded_at  timestamp_ltz default current_timestamp()
);

copy into raw_transcripts (payload)
from @transcripts_stage
file_format = (type = json strip_outer_array = true);

create or replace table transcript_paragraphs as
with lines as (
    select r.payload:symbol::string as symbol,
           r.payload:year::int as fiscal_year,
           r.payload:period::string as fiscal_period,
           r.payload:date::date as report_date,
           l.index as line_number,
           regexp_substr(l.value, '^([A-Z][^:]{0,60}):\\s', 1, 1, 'e', 1) as speaker_label,
           trim(iff(speaker_label is null, l.value,
                    substr(l.value, length(speaker_label) + 2))) as content
    from raw_transcripts r,
         lateral split_to_table(r.payload:content::string, '\n') l
    where trim(l.value) <> ''
)
select symbol, fiscal_year, fiscal_period, report_date,
       row_number() over (partition by symbol, fiscal_year, fiscal_period
                          order by line_number) as paragraph_number,
       last_value(speaker_label ignore nulls) over (
           partition by symbol, fiscal_year, fiscal_period
           order by line_number
           rows between unbounded preceding and current row) as speaker,
       content
from lines;

select symbol, fiscal_year, fiscal_period,
       count(*) as paragraphs,
       count(distinct speaker) as speakers
from transcript_paragraphs
group by 1, 2, 3
having count(distinct speaker) < 3 or count(*) < 10
order by symbol;