subscribers_stat_amendments

In order to update data in channels_stat_daily clickhouse table for subscribers events, there has been created a proxy table subscription_stat_events_amendments which takes data to be proxied to channels_stat_daily via subscription_events_amendments_channel_events materialized view

The next insert statement was produced in order to fix subscribers data duplicates since ‘2024-04-01’:

insert into subscription_stat_events_amendments (
    timestamp,     
    subscriber_id,     
    message_example_id,     
    user_country,     
    user_browser,     
    user_browser_language,     
    channel_id,     
    active_campaigns,     
    campaign_id,     
    subscribe_count,     
    unsubscribe_count,     
    window_id,     
    window_pool_id,     
    firebase_app,     
    firebase_id,     
    subscriber_age 
) select  
    timestamp,     
    subscriber_id,     
    message_example_id,     
    user_country,     
    user_browser,     
    user_browser_language,     
    channel_id,     
    active_campaigns,     
    campaign_id,     
    subscribe_count * -1 as subscribe_count,     
    unsubscribe_count * -1 as unsubscribe_count,     
    window_id,     
    window_pool_id,     
    firebase_app,     
    firebase_id,     
    subscriber_age 
from subscription_events where toDate(timestamp) <= '2024-04-01';