import duckdb
with duckdb.connect('ddb.db') as conn:
    conn.execute(fr"""
            CREATE OR REPLACE TABLE 'everflow_daily' AS SELECT * FROM read_csv_auto('myexport/*.csv', union_by_name = true, filename = true);
        UPDATE everflow_daily SET sub5 = replace(sub5, '''', '');
        UPDATE everflow_daily SET sub5 = lower(trim(sub5));
        UPDATE everflow_daily SET sub5 = NULL WHERE sub5 = 'n/a';
        UPDATE everflow_daily SET sub4 = NULL WHERE sub4 = 'N/A';
        UPDATE everflow_daily SET sub3 = NULL WHERE sub3 = 'N/A';
        ALTER TABLE everflow_daily
        ADD offer_type VARCHAR(32);

        UPDATE everflow_daily
        SET offer_type = CASE 
            WHEN UPPER(offer_name) LIKE '%CPA%' THEN 'CPA'
            WHEN UPPER(offer_name) LIKE '%CPC%' THEN 'CPC'
            WHEN UPPER(offer_name) LIKE '%CPL%' THEN 'CPL'
            ELSE 'Other'
        END;
            """)

select sub4, sum(revenue), len(sub4), count(*) from everflow_daily
where network_affiliate_id <> 16201
and sub4 = 'N/A'
or sub4 = ''
or sub4 is null
group by 1
order by 2 desc