CREATE STREAM APARAT_WATCH (ip VARCHAR, req_ip VARCHAR, vid BIGINT, uid VARCHAR, userid BIGINT, afcn VARCHAR, session_id VARCHAR, profile VARCHAR, watch DOUBLE, progress DOUBLE, position BIGINT, duration VARCHAR, timestamp BIGINT, payload_time BIGINT, chunkname VARCHAR, download INTEGER, watch_type VARCHAR) WITH (kafka_topic='aparat_watch', value_format='JSON', timestamp='timestamp');CREATE STREAM APARAT_WATCH_WITH_PROPER_KEY
WITH(KAFKA_TOPIC='aparat-watch-with-proper-key', TIMESTAMP='TIMESTAMP') AS
SELECT (AFCN + '.' + CAST (VID AS STRING) + '.') AS ID, VID, UID, AFCN, USERID, session_id AS SESSIONID, PROGRESS, POSITION, PROFILE, TIMESTAMP, CHUNKNAME, DOWNLOAD, WATCH_TYPE
FROM APARAT_WATCH
PARTITION BY ID;CREATE TABLE APARAT_WATCH_HOURLY_AGG AS
SELECT ID, TOPKDISTINCT(AFCN,1)[0] AS AFCN, TOPKDISTINCT(UID, 1)[0] AS UID, TOPKDISTINCT(VID, 1)[0] AS VID, TOPKDISTINCT(SESSIONID, 1)[0] AS SESSIONID, MAX(progress) AS progress, MAX(position) AS position, MAX(timestamp) AS timestamp, TOPKDISTINCT(CHUNKNAME,1)[0] AS chunkname, MAX(DOWNLOAD) AS download
FROM APARAT_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 HOUR)
WHERE progress IS NOT NULL and position IS NOT NULL
GROUP BY ID;CREATE TABLE APARAT_WATCH_DAILY_AGG AS
SELECT ID, TOPKDISTINCT(AFCN,1)[0] AS AFCN, TOPKDISTINCT(UID, 1)[0] AS UID, TOPKDISTINCT(VID, 1)[0] AS VID, TOPKDISTINCT(SESSIONID, 1)[0] AS SESSIONID, MAX(progress) AS progress, MAX(position) AS position, MAX(timestamp) AS timestamp, TOPKDISTINCT(CHUNKNAME,1)[0] AS chunkname, MAX(DOWNLOAD) AS download
FROM APARAT_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL
GROUP BY ID;CREATE STREAM FILIMO_WATCH (ip VARCHAR, req_ip VARCHAR, vid BIGINT, uid VARCHAR, userid BIGINT, afcn VARCHAR, session_id VARCHAR, profile VARCHAR, watch DOUBLE, progress DOUBLE, position BIGINT, duration VARCHAR, timestamp BIGINT, payload_time BIGINT, chunkname VARCHAR, download INTEGER, watch_type VARCHAR) WITH (kafka_topic='filimo_watch', value_format='JSON', timestamp='timestamp');CREATE STREAM FILIMO_WATCH_WITH_PROPER_KEY
WITH(KAFKA_TOPIC='filimo-watch-with-proper-key', TIMESTAMP='TIMESTAMP') AS
SELECT (AFCN + '.' + CAST (VID AS STRING) + '.') AS ID, VID, UID, AFCN, USERID, session_id AS SESSIONID, PROGRESS, POSITION, PROFILE, TIMESTAMP, CHUNKNAME, DOWNLOAD, WATCH_TYPE
FROM FILIMO_WATCH
PARTITION BY ID;CREATE TABLE FILIMO_WATCH_HOURLY_AGG AS
SELECT ID, TOPKDISTINCT(AFCN,1)[0] AS AFCN, TOPKDISTINCT(UID, 1)[0] AS UID, TOPKDISTINCT(VID, 1)[0] AS VID, TOPKDISTINCT(SESSIONID, 1)[0] AS SESSIONID, MAX(progress) AS progress, MAX(position) AS position, MAX(timestamp) AS timestamp, TOPKDISTINCT(CHUNKNAME,1)[0] AS chunkname, MAX(DOWNLOAD) AS download
FROM FILIMO_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 HOUR)
WHERE progress IS NOT NULL and position IS NOT NULL
GROUP BY ID;CREATE TABLE FILIMO_WATCH_DAILY_AGG AS
SELECT ID, TOPKDISTINCT(AFCN,1)[0] AS AFCN, TOPKDISTINCT(UID, 1)[0] AS UID, TOPKDISTINCT(VID, 1)[0] AS VID, TOPKDISTINCT(SESSIONID, 1)[0] AS SESSIONID, MAX(progress) AS progress, MAX(position) AS position, MAX(timestamp) AS timestamp, TOPKDISTINCT(CHUNKNAME,1)[0] AS chunkname, MAX(DOWNLOAD) AS download
FROM FILIMO_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL
GROUP BY ID;CREATE TABLE APARAT_VIDEO_UNIQUE_VISIT_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT VID, UID, AFCN, SESSIONID, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, COUNT(AFCN) AS UNIQUE_VISIT
FROM APARAT_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL and download=0
GROUP BY VID, UID, AFCN, SESSIONID;CREATE TABLE APARAT_VIDEO_VISIT_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT VID, UID, AFCN, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, COUNT(AFCN) AS VISIT
FROM APARAT_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL and download=0
GROUP BY VID, UID, AFCN;CREATE TABLE APARAT_VIDEO_WATCH_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT VID, UID, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, SUM(POSITION) AS WATCH_TIME
FROM APARAT_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL and download=0
GROUP BY VID, UID;CREATE TABLE APARAT_VIDEO_PROGRESS_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT VID, UID, AFCN, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, MAX(PROGRESS) AS PROGRESS
FROM APARAT_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL and download=0
GROUP BY VID, UID, AFCN;CREATE TABLE FILIMO_VIDEO_UNIQUE_VISIT_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT VID, UID, AFCN, SESSIONID, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, COUNT(AFCN) AS UNIQUE_VISIT
FROM FILIMO_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL and download=0
GROUP BY VID, UID, AFCN, SESSIONID;CREATE TABLE FILIMO_VIDEO_VISIT_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT VID, UID, AFCN, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, COUNT(AFCN) AS VISIT
FROM FILIMO_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL and download=0
GROUP BY VID, UID, AFCN;CREATE TABLE FILIMO_VIDEO_WATCH_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT VID, UID, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, SUM(POSITION) AS WATCH_TIME
FROM FILIMO_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL and download=0
GROUP BY VID, UID;CREATE TABLE FILIMO_VIDEO_PROGRESS_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT VID, UID, AFCN, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, MAX(PROGRESS) AS PROGRESS
FROM FILIMO_WATCH_WITH_PROPER_KEY
WINDOW TUMBLING (SIZE 1 DAY)
WHERE progress IS NOT NULL and position IS NOT NULL and download=0
GROUP BY VID, UID, AFCN;When using AVRO, there should be a "[Topic Name]-value" schema in schema-registry with typed properties according to ksql query
PUT http://kfk1.saba-e.com:8083/connectors/aparat-hourly-watch-mongo/config
{
"topics": "APARAT_WATCH_HOURLY_AGG",
"connector.class": "com.datamountaineer.streamreactor.connect.mongodb.sink.MongoSinkConnector",
"name": "aparat-watch-mongo",
"tasks.max": "1",
"connect.progress.enabled": true,
"connect.mongo.connection": "mongodb://kafka:[email protected]:37017/aparat?w=1&journal=true",
"connect.mongo.db": "aparat",
"connect.mongo.kcql": "UPSERT INTO aparat_watch SELECT * FROM APARAT_WATCH_HOURLY_AGG PK ID",
"config.action.reload": "RESTART",
"errors.log.include.messages": true,
"errors.deadletterqueue.topic.name": "aparat_watch_mongo_dlq",
"connect.mongo.username": "kafka",
"connect.mongo.password": "tHe51ODj1B0Y",
"connect.mongo.error.policy": "RETRY",
"connect.mongo.batch.size": "5",
"connect.mongo.max.retries": "5",
"transforms": "id_to_object",
"transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
"transforms.id_to_object.field": "id",
"value.converter.schemas.enable": "false",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"key.converter.schemas.enable": "false",
"key.converter": "org.apache.kafka.connect.storage.StringConverter"
}PUT http://kfk1.saba-e.com:8083/connectors/aparat-daily-watch-mongo/config
{
"topics": "APARAT_WATCH_DAILY_AGG",
"connector.class": "com.datamountaineer.streamreactor.connect.mongodb.sink.MongoSinkConnector",
"name": "aparat-watch-mongo",
"tasks.max": "1",
"connect.progress.enabled": true,
"connect.mongo.connection": "mongodb://kafka:[email protected]:37017/aparat?w=1&journal=true",
"connect.mongo.db": "aparat",
"connect.mongo.kcql": "UPSERT INTO aparat_watch_daily SELECT * FROM APARAT_WATCH_DAILY_AGG PK ID",
"config.action.reload": "RESTART",
"errors.log.include.messages": true,
"errors.deadletterqueue.topic.name": "aparat_watch_mongo_dlq",
"connect.mongo.username": "kafka",
"connect.mongo.password": "tHe51ODj1B0Y",
"connect.mongo.error.policy": "RETRY",
"connect.mongo.batch.size": "5",
"connect.mongo.max.retries": "5",
"transforms": "id_to_object",
"transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
"transforms.id_to_object.field": "id",
"value.converter.schemas.enable": "false",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"key.converter.schemas.enable": "false",
"key.converter": "org.apache.kafka.connect.storage.StringConverter"
}PUT http://kfk1.saba-e.com:8083/connectors/aparat-hourly-watch-elastic/config
{
"topics": "APARAT_WATCH_HOURLY_AGG",
"connector.class": "com.datamountaineer.streamreactor.connect.elastic6.ElasticSinkConnector",
"name": "aparat-watch-elastic",
"tasks.max": "1",
"connect.progress.enabled": true,
"connect.elastic.url": "els9.saba-e.com:19200",
"connect.elastic.use.http": "http",
"connect.elastic.use.http.username": "",
"connect.elastic.use.http.password": "",
"connect.elastic.batch.size": "4000",
"connect.elastic.kcql": "UPSERT INTO aparat_watch SELECT * FROM APARAT_WATCH_HOURLY_AGG PK ID",
"config.action.reload": "RESTART",
"errors.log.include.messages": true,
"errors.deadletterqueue.topic.name": "aparat_watch_elastic_dlq",
"transforms": "id_to_object",
"transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
"transforms.id_to_object.field": "id",
"value.converter.schemas.enable": "false",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"key.converter.schemas.enable": "false",
"key.converter": "org.apache.kafka.connect.storage.StringConverter"
}PUT http://kfk1.saba-e.com:8083/connectors/aparat-hourly-watch-mongo/config
{
"connector.class": "at.grahsl.kafka.connect.mongodb.MongoDbSinkConnector",
"topics": "APARAT_WATCH_HOURLY_AGG",
"mongodb.connection.uri": "mongodb://kafka:[email protected]:37017/aparat?w=1&journal=true",
"name": "aparat-hourly-watch-mongo",
"value.converter.schemas.enable": "false",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"key.converter.schemas.enable": "false",
"key.converter": "org.apache.kafka.connect.storage.StringConverter",
"mongodb.collection": "aparat_hourly_watch",
"transforms": "id_to_object",
"transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
"transforms.id_to_object.field": "id",
"mongodb.document.id.strategy": "at.grahsl.kafka.connect.mongodb.processor.id.strategy.FullKeyStrategy"
}PUT http://kfk1.saba-e.com:8083/connectors/aparat-daily-watch-mongo/config
{
"connector.class": "at.grahsl.kafka.connect.mongodb.MongoDbSinkConnector",
"topics": "APARAT_WATCH_DAILY_AGG",
"mongodb.connection.uri": "mongodb://kafka:[email protected]:37017/aparat?w=1&journal=true",
"name": "aparat-daily-watch-mongo",
"value.converter.schemas.enable": "false",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"key.converter.schemas.enable": "false",
"key.converter": "org.apache.kafka.connect.storage.StringConverter",
"mongodb.collection": "aparat_daily_watch",
"transforms": "id_to_object",
"transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
"transforms.id_to_object.field": "id",
"mongodb.document.id.strategy": "at.grahsl.kafka.connect.mongodb.processor.id.strategy.FullKeyStrategy"
}CREATE STREAM APARAT_RAW_ACTIVITY AS
SELECT AFCN, 1 AS COUNT, 1 AS GROUPID
FROM APARAT_WATCH
PARTITION BY AFCN;CREATE TABLE APARAT_AGGREGATED_ACTIVITY AS
SELECT AFCN, SUM(COUNT) AS COUNT, GROUPID
FROM APARAT_RAW_ACTIVITY
WINDOW TUMBLING (SIZE 1 MINUTE)
GROUP BY AFCN, GROUPID;/* Not Used */
CREATE TABLE APARAT_TOP_AFCNS AS
SELECT TOPKDISTINCT(AFCN, 10000) AS AFCNS, GROUPID
FROM APARAT_RAW_ACTIVITY
GROUP BY GROUPID;CREATE STREAM FILIMO_RAW_ACTIVITY AS
SELECT AFCN, 1 AS COUNT, 1 AS GROUPID
FROM FILIMO_WATCH
PARTITION BY AFCN;CREATE TABLE FILIMO_AGGREGATED_ACTIVITY AS
SELECT AFCN, SUM(COUNT) AS COUNT, GROUPID
FROM FILIMO_RAW_ACTIVITY
WINDOW TUMBLING (SIZE 1 MINUTE)
GROUP BY AFCN, GROUPID;/* Not Used */
CREATE TABLE FILIMO_TOP_AFCNS AS
SELECT TOPKDISTINCT(AFCN, 10000) AS AFCNS, GROUPID
FROM FILIMO_RAW_ACTIVITY
GROUP BY GROUPID;MongoDB collections should have index for all fields
db.getCollection("aparat_daily_watch").createIndex({ "VID": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "UID": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "SESSIONID": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "POSITION": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "PROGRESS": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "TIMESTAMP": 1 }); // Do we need to set a 31536000 seconds (1 year) retention for it?
db.getCollection("aparat_daily_watch").createIndex({ "ID": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "AFCN": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "DOWNLOAD": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "CHUNKNAME": 1 });Hourly collection shouldn't be kept longer than a month
db.getCollection("aparat_hourly_watch").createIndex({ "VID": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "UID": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "SESSIONID": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "POSITION": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "PROGRESS": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "TIMESTAMP": 1 }, { "expireAfterSeconds": 2592000 }); // 1 month retention
db.getCollection("aparat_hourly_watch").createIndex({ "ID": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "AFCN": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "DOWNLOAD": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "CHUNKNAME": 1 });MongoDB collections should have index for all fields
db.getCollection("filimo_daily_watch").createIndex({ "VID": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "UID": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "SESSIONID": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "POSITION": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "PROGRESS": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "TIMESTAMP": 1 }); // Do we need to set a 31536000 seconds (1 year) retention for it?
db.getCollection("filimo_daily_watch").createIndex({ "ID": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "AFCN": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "DOWNLOAD": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "CHUNKNAME": 1 });Hourly collection shouldn't be kept longer than a month
db.getCollection("filimo_hourly_watch").createIndex({ "VID": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "UID": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "SESSIONID": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "POSITION": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "PROGRESS": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "TIMESTAMP": 1 }, { "expireAfterSeconds": 2592000 }); // 1 month retention
db.getCollection("filimo_hourly_watch").createIndex({ "ID": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "AFCN": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "DOWNLOAD": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "CHUNKNAME": 1 });CREATE STREAM ANALYTICS (afcn VARCHAR, source VARCHAR, user VARCHAR, action VARCHAR, session VARCHAR, id VARCHAR, section VARCHAR, type VARCHAR, url VARCHAR, TIMESTAMP VARCHAR, "PROPERTIES" MAP<VARCHAR, VARCHAR>) WITH (kafka_topic='analytics', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');
CREATE STREAM ANALYTICS_WITH_PROPER_KEY WITH(KAFKA_TOPIC='analytics_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
SELECT (id + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(id AS BIGINT) AS videoid, source, action, afcn, user, session, section, type, url, TIMESTAMP, "PROPERTIES"
FROM ANALYTICS
PARTITION BY "ID";
CREATE STREAM ANALYTICS_ALL_EVENTS (SOURCE VARCHAR, VIDEOID BIGINT, TIMESTAMP VARCHAR, RECOMMENDATION_IMPRESSIONS INTEGER, RECOMMENDATION_PAGEVIEWS INTEGER, RECOMMENDATION_BOOSTED_PAGEVIEWS INTEGER, RECOMMENDATION_CLICKS INTEGER, RECOMMENDATION_RIGHT_CLICKS INTEGER, RECOMMENDATION_DISPLAYS INTEGER, RECOMMENDATION_DISPLAY_CANDIDATES INTEGER, RECOMMENDATION_BOOSTED_IMPRESSIONS INTEGER, RECOMMENDATION_BOOSTED_CLICKS INTEGER, RECOMMENDATION_BOOSTED_RIGHT_CLICKS INTEGER, RECOMMENDATION_BOOSTED_CTA_CLICKS INTEGER, RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS INTEGER, SEARCH_RESULT_IMPRESSIONS INTEGER, SEARCH_RESULT_CLICKS INTEGER, SEARCH_RESULT_RIGHT_CLICKS INTEGER, SEARCH_RESULT_BOOSTED_IMPRESSIONS INTEGER, SEARCH_RESULT_BOOSTED_CLICKS INTEGER, SEARCH_RESULT_BOOSTED_RIGHT_CLICKS INTEGER, SEARCH_RESULT_DISPLAYS INTEGER, SEARCH_RESULT_DISPLAY_CANDIDATES INTEGER, SEARCH_SUGGESTION_IMPRESSIONS INTEGER, SEARCH_SUGGESTION_CLICKS INTEGER, SEARCH_SUGGESTION_RIGHT_CLICKS INTEGER, SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS INTEGER, SEARCH_SUGGESTION_BOOSTED_CLICKS INTEGER, SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS INTEGER, SEARCH_SUGGESTION_DISPLAYS INTEGER, SEARCH_SUGGESTION_DISPLAY_CANDIDATES INTEGER) WITH (KAFKA_TOPIC='analytics_all_events', VALUE_FORMAT='json', KEY='VIDEOID');
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 1 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='impression' and type != 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 1 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE action='pageview' and type != 'boosted' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 1 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE action='pageview' and type='boosted' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 1 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='click' and type != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 1 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='right_click' and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 1 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='display' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 1 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='display_candidate' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 1 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='impression' and type='boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 1 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 1 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='right_click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 1 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='click' and TYPE = 'boosted-cta' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 1 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='right_click' and TYPE = 'boosted-cta' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 1 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='impression' and type != 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 1 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='click' and type != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 1 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='right_click' and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 1 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='impression' and type='boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 1 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 1 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='right_click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;
/*
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 1 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action="..." PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 1 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action="..." PARTITION BY VIDEOID;
*/
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 1 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='impression' and type != 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 1 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='click' and type != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 1 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='right_click' and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 1 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='impression' and type='boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 1 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 1 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='right_click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;
/*
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 1 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action="..." PARTITION BY VIDEOID;
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 1 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action="..." PARTITION BY VIDEOID;
*/
/* Use these two instead of laters when MAX(BIGINT) is supported */
/*
CREATE TABLE ANALYTICS_APARAT_DAILY_AGGREGATED WITH (VALUE_FORMAT='AVRO') AS SELECT VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(TIMESTAMP, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS TIMESTAMP, SUM(RECOMMENDATION_IMPRESSIONS) as RECOMMENDATION_IMPRESSIONS, SUM(RECOMMENDATION_PAGEVIEWS) as RECOMMENDATION_PAGEVIEWS, SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS) as RECOMMENDATION_BOOSTED_PAGEVIEWS, SUM(RECOMMENDATION_CLICKS) as RECOMMENDATION_CLICKS, SUM(RECOMMENDATION_RIGHT_CLICKS) as RECOMMENDATION_RIGHT_CLICKS, SUM(RECOMMENDATION_DISPLAYS) as RECOMMENDATION_DISPLAYS, SUM(RECOMMENDATION_DISPLAY_CANDIDATES) as RECOMMENDATION_DISPLAY_CANDIDATES, SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS) as RECOMMENDATION_BOOSTED_IMPRESSIONS, SUM(RECOMMENDATION_BOOSTED_CLICKS) as RECOMMENDATION_BOOSTED_CLICKS, SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS) as RECOMMENDATION_BOOSTED_CTA_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, SUM(SEARCH_RESULT_IMPRESSIONS) as SEARCH_RESULT_IMPRESSIONS, SUM(SEARCH_RESULT_CLICKS) as SEARCH_RESULT_CLICKS, SUM(SEARCH_RESULT_RIGHT_CLICKS) as SEARCH_RESULT_RIGHT_CLICKS, SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS) as SEARCH_RESULT_BOOSTED_IMPRESSIONS, SUM(SEARCH_RESULT_BOOSTED_CLICKS) as SEARCH_RESULT_BOOSTED_CLICKS, SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS) as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_RESULT_DISPLAYS) as SEARCH_RESULT_DISPLAYS, SUM(SEARCH_RESULT_DISPLAY_CANDIDATES) as SEARCH_RESULT_DISPLAY_CANDIDATES, SUM(SEARCH_SUGGESTION_IMPRESSIONS) as SEARCH_SUGGESTION_IMPRESSIONS, SUM(SEARCH_SUGGESTION_CLICKS) as SEARCH_SUGGESTION_CLICKS, SUM(SEARCH_SUGGESTION_RIGHT_CLICKS) as SEARCH_SUGGESTION_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS) as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS) as SEARCH_SUGGESTION_BOOSTED_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS) as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_DISPLAYS) as SEARCH_SUGGESTION_DISPLAYS, SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES) as SEARCH_SUGGESTION_DISPLAY_CANDIDATES, (SUM(RECOMMENDATION_IMPRESSIONS)+SUM(RECOMMENDATION_PAGEVIEWS)+SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS)+SUM(RECOMMENDATION_CLICKS)+SUM(RECOMMENDATION_RIGHT_CLICKS)+SUM(RECOMMENDATION_DISPLAYS)+SUM(RECOMMENDATION_DISPLAY_CANDIDATES)+SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS)+SUM(RECOMMENDATION_BOOSTED_CLICKS)+SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS)+SUM(SEARCH_RESULT_IMPRESSIONS)+SUM(SEARCH_RESULT_CLICKS)+SUM(SEARCH_RESULT_RIGHT_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS)+SUM(SEARCH_RESULT_BOOSTED_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_RESULT_DISPLAYS)+SUM(SEARCH_RESULT_DISPLAY_CANDIDATES)+SUM(SEARCH_SUGGESTION_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_CLICKS)+SUM(SEARCH_SUGGESTION_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_DISPLAYS)+SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES)) as OVERALL FROM ANALYTICS_ALL_EVENTS WINDOW TUMBLING (SIZE 1 DAY) WHERE VIDEOID is not null and (SOURCE='aparat' or SOURCE='aparat_web' or SOURCE='aparat_android' or SOURCE='aparat_ios') GROUP BY VIDEOID;
CREATE TABLE ANALYTICS_FILIMO_DAILY_AGGREGATED WITH (VALUE_FORMAT='AVRO') AS SELECT VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(TIMESTAMP, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS TIMESTAMP, SUM(RECOMMENDATION_IMPRESSIONS) as RECOMMENDATION_IMPRESSIONS, SUM(RECOMMENDATION_PAGEVIEWS) as RECOMMENDATION_PAGEVIEWS, SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS) as RECOMMENDATION_BOOSTED_PAGEVIEWS, SUM(RECOMMENDATION_CLICKS) as RECOMMENDATION_CLICKS, SUM(RECOMMENDATION_RIGHT_CLICKS) as RECOMMENDATION_RIGHT_CLICKS, SUM(RECOMMENDATION_DISPLAYS) as RECOMMENDATION_DISPLAYS, SUM(RECOMMENDATION_DISPLAY_CANDIDATES) as RECOMMENDATION_DISPLAY_CANDIDATES, SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS) as RECOMMENDATION_BOOSTED_IMPRESSIONS, SUM(RECOMMENDATION_BOOSTED_CLICKS) as RECOMMENDATION_BOOSTED_CLICKS, SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS) as RECOMMENDATION_BOOSTED_CTA_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, SUM(SEARCH_RESULT_IMPRESSIONS) as SEARCH_RESULT_IMPRESSIONS, SUM(SEARCH_RESULT_CLICKS) as SEARCH_RESULT_CLICKS, SUM(SEARCH_RESULT_RIGHT_CLICKS) as SEARCH_RESULT_RIGHT_CLICKS, SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS) as SEARCH_RESULT_BOOSTED_IMPRESSIONS, SUM(SEARCH_RESULT_BOOSTED_CLICKS) as SEARCH_RESULT_BOOSTED_CLICKS, SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS) as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_RESULT_DISPLAYS) as SEARCH_RESULT_DISPLAYS, SUM(SEARCH_RESULT_DISPLAY_CANDIDATES) as SEARCH_RESULT_DISPLAY_CANDIDATES, SUM(SEARCH_SUGGESTION_IMPRESSIONS) as SEARCH_SUGGESTION_IMPRESSIONS, SUM(SEARCH_SUGGESTION_CLICKS) as SEARCH_SUGGESTION_CLICKS, SUM(SEARCH_SUGGESTION_RIGHT_CLICKS) as SEARCH_SUGGESTION_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS) as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS) as SEARCH_SUGGESTION_BOOSTED_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS) as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_DISPLAYS) as SEARCH_SUGGESTION_DISPLAYS, SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES) as SEARCH_SUGGESTION_DISPLAY_CANDIDATES, (SUM(RECOMMENDATION_IMPRESSIONS)+SUM(RECOMMENDATION_PAGEVIEWS)+SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS)+SUM(RECOMMENDATION_CLICKS)+SUM(RECOMMENDATION_RIGHT_CLICKS)+SUM(RECOMMENDATION_DISPLAYS)+SUM(RECOMMENDATION_DISPLAY_CANDIDATES)+SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS)+SUM(RECOMMENDATION_BOOSTED_CLICKS)+SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS)+SUM(SEARCH_RESULT_IMPRESSIONS)+SUM(SEARCH_RESULT_CLICKS)+SUM(SEARCH_RESULT_RIGHT_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS)+SUM(SEARCH_RESULT_BOOSTED_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_RESULT_DISPLAYS)+SUM(SEARCH_RESULT_DISPLAY_CANDIDATES)+SUM(SEARCH_SUGGESTION_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_CLICKS)+SUM(SEARCH_SUGGESTION_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_DISPLAYS)+SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES)) as OVERALL FROM ANALYTICS_ALL_EVENTS WINDOW TUMBLING (SIZE 1 DAY) WHERE VIDEOID is not null and (SOURCE='filimo' or SOURCE='filimo_web' or SOURCE='filimo_android' or SOURCE='filimo_ios') GROUP BY VIDEOID;
*/
CREATE TABLE ANALYTICS_APARAT_DAILY_AGGREGATED WITH (VALUE_FORMAT='AVRO') AS SELECT VIDEOID, TIMESTAMPTOSTRING(CAST(MAX(CAST(STRINGTOTIMESTAMP(TIMESTAMP, 'yyyy-MM-dd''T''HH:mm:ssX')/1000 AS INT)) AS BIGINT) * 1000, 'yyyy-MM-dd') AS TIMESTAMP, SUM(RECOMMENDATION_IMPRESSIONS) as RECOMMENDATION_IMPRESSIONS, SUM(RECOMMENDATION_PAGEVIEWS) as RECOMMENDATION_PAGEVIEWS, SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS) as RECOMMENDATION_BOOSTED_PAGEVIEWS, SUM(RECOMMENDATION_CLICKS) as RECOMMENDATION_CLICKS, SUM(RECOMMENDATION_RIGHT_CLICKS) as RECOMMENDATION_RIGHT_CLICKS, SUM(RECOMMENDATION_DISPLAYS) as RECOMMENDATION_DISPLAYS, SUM(RECOMMENDATION_DISPLAY_CANDIDATES) as RECOMMENDATION_DISPLAY_CANDIDATES, SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS) as RECOMMENDATION_BOOSTED_IMPRESSIONS, SUM(RECOMMENDATION_BOOSTED_CLICKS) as RECOMMENDATION_BOOSTED_CLICKS, SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS) as RECOMMENDATION_BOOSTED_CTA_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, SUM(SEARCH_RESULT_IMPRESSIONS) as SEARCH_RESULT_IMPRESSIONS, SUM(SEARCH_RESULT_CLICKS) as SEARCH_RESULT_CLICKS, SUM(SEARCH_RESULT_RIGHT_CLICKS) as SEARCH_RESULT_RIGHT_CLICKS, SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS) as SEARCH_RESULT_BOOSTED_IMPRESSIONS, SUM(SEARCH_RESULT_BOOSTED_CLICKS) as SEARCH_RESULT_BOOSTED_CLICKS, SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS) as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_RESULT_DISPLAYS) as SEARCH_RESULT_DISPLAYS, SUM(SEARCH_RESULT_DISPLAY_CANDIDATES) as SEARCH_RESULT_DISPLAY_CANDIDATES, SUM(SEARCH_SUGGESTION_IMPRESSIONS) as SEARCH_SUGGESTION_IMPRESSIONS, SUM(SEARCH_SUGGESTION_CLICKS) as SEARCH_SUGGESTION_CLICKS, SUM(SEARCH_SUGGESTION_RIGHT_CLICKS) as SEARCH_SUGGESTION_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS) as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS) as SEARCH_SUGGESTION_BOOSTED_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS) as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_DISPLAYS) as SEARCH_SUGGESTION_DISPLAYS, SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES) as SEARCH_SUGGESTION_DISPLAY_CANDIDATES, (SUM(RECOMMENDATION_IMPRESSIONS)+SUM(RECOMMENDATION_PAGEVIEWS)+SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS)+SUM(RECOMMENDATION_CLICKS)+SUM(RECOMMENDATION_RIGHT_CLICKS)+SUM(RECOMMENDATION_DISPLAYS)+SUM(RECOMMENDATION_DISPLAY_CANDIDATES)+SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS)+SUM(RECOMMENDATION_BOOSTED_CLICKS)+SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS)+SUM(SEARCH_RESULT_IMPRESSIONS)+SUM(SEARCH_RESULT_CLICKS)+SUM(SEARCH_RESULT_RIGHT_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS)+SUM(SEARCH_RESULT_BOOSTED_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_RESULT_DISPLAYS)+SUM(SEARCH_RESULT_DISPLAY_CANDIDATES)+SUM(SEARCH_SUGGESTION_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_CLICKS)+SUM(SEARCH_SUGGESTION_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_DISPLAYS)+SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES)) as OVERALL FROM ANALYTICS_ALL_EVENTS WINDOW TUMBLING (SIZE 1 DAY) WHERE VIDEOID is not null and (SOURCE='aparat' or SOURCE='aparat_web' or SOURCE='aparat_android' or SOURCE='aparat_ios') GROUP BY VIDEOID;
CREATE TABLE ANALYTICS_FILIMO_DAILY_AGGREGATED WITH (VALUE_FORMAT='AVRO') AS SELECT VIDEOID, TIMESTAMPTOSTRING(CAST(MAX(CAST(STRINGTOTIMESTAMP(TIMESTAMP, 'yyyy-MM-dd''T''HH:mm:ssX')/1000 AS INT)) AS BIGINT) * 1000, 'yyyy-MM-dd') AS TIMESTAMP, SUM(RECOMMENDATION_IMPRESSIONS) as RECOMMENDATION_IMPRESSIONS, SUM(RECOMMENDATION_PAGEVIEWS) as RECOMMENDATION_PAGEVIEWS, SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS) as RECOMMENDATION_BOOSTED_PAGEVIEWS, SUM(RECOMMENDATION_CLICKS) as RECOMMENDATION_CLICKS, SUM(RECOMMENDATION_RIGHT_CLICKS) as RECOMMENDATION_RIGHT_CLICKS, SUM(RECOMMENDATION_DISPLAYS) as RECOMMENDATION_DISPLAYS, SUM(RECOMMENDATION_DISPLAY_CANDIDATES) as RECOMMENDATION_DISPLAY_CANDIDATES, SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS) as RECOMMENDATION_BOOSTED_IMPRESSIONS, SUM(RECOMMENDATION_BOOSTED_CLICKS) as RECOMMENDATION_BOOSTED_CLICKS, SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS) as RECOMMENDATION_BOOSTED_CTA_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, SUM(SEARCH_RESULT_IMPRESSIONS) as SEARCH_RESULT_IMPRESSIONS, SUM(SEARCH_RESULT_CLICKS) as SEARCH_RESULT_CLICKS, SUM(SEARCH_RESULT_RIGHT_CLICKS) as SEARCH_RESULT_RIGHT_CLICKS, SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS) as SEARCH_RESULT_BOOSTED_IMPRESSIONS, SUM(SEARCH_RESULT_BOOSTED_CLICKS) as SEARCH_RESULT_BOOSTED_CLICKS, SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS) as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_RESULT_DISPLAYS) as SEARCH_RESULT_DISPLAYS, SUM(SEARCH_RESULT_DISPLAY_CANDIDATES) as SEARCH_RESULT_DISPLAY_CANDIDATES, SUM(SEARCH_SUGGESTION_IMPRESSIONS) as SEARCH_SUGGESTION_IMPRESSIONS, SUM(SEARCH_SUGGESTION_CLICKS) as SEARCH_SUGGESTION_CLICKS, SUM(SEARCH_SUGGESTION_RIGHT_CLICKS) as SEARCH_SUGGESTION_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS) as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS) as SEARCH_SUGGESTION_BOOSTED_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS) as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_DISPLAYS) as SEARCH_SUGGESTION_DISPLAYS, SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES) as SEARCH_SUGGESTION_DISPLAY_CANDIDATES, (SUM(RECOMMENDATION_IMPRESSIONS)+SUM(RECOMMENDATION_PAGEVIEWS)+SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS)+SUM(RECOMMENDATION_CLICKS)+SUM(RECOMMENDATION_RIGHT_CLICKS)+SUM(RECOMMENDATION_DISPLAYS)+SUM(RECOMMENDATION_DISPLAY_CANDIDATES)+SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS)+SUM(RECOMMENDATION_BOOSTED_CLICKS)+SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS)+SUM(SEARCH_RESULT_IMPRESSIONS)+SUM(SEARCH_RESULT_CLICKS)+SUM(SEARCH_RESULT_RIGHT_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS)+SUM(SEARCH_RESULT_BOOSTED_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_RESULT_DISPLAYS)+SUM(SEARCH_RESULT_DISPLAY_CANDIDATES)+SUM(SEARCH_SUGGESTION_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_CLICKS)+SUM(SEARCH_SUGGESTION_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_DISPLAYS)+SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES)) as OVERALL FROM ANALYTICS_ALL_EVENTS WINDOW TUMBLING (SIZE 1 DAY) WHERE VIDEOID is not null and (SOURCE='filimo' or SOURCE='filimo_web' or SOURCE='filimo_android' or SOURCE='filimo_ios') GROUP BY VIDEOID;CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_IMPRESSION (afcn VARCHAR, user VARCHAR, session VARCHAR, id VARCHAR, type VARCHAR, url VARCHAR, timestamp VARCHAR) WITH (kafka_topic='analytics_aparat_recommendation_impression', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');
CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_IMPRESSION_WITH_PROPER_KEY WITH(KAFKA_TOPIC='analytics_aparat_recommendation_impression_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
SELECT (ID + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(ID AS BIGINT) AS VIDEOID, AFCN, SESSION, TYPE, URL, TIMESTAMP
FROM ANALYTICS_APARAT_RECOMMENDATION_IMPRESSION
PARTITION BY ID;
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_IMPRESSION_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_recommendation_impression_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null
GROUP BY ID, VIDEOID;
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOST_IMPRESSION_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_recommendation_impression_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null and TYPE = 'boosted-brc'
GROUP BY ID, VIDEOID;CREATE STREAM ANALYTICS_APARAT_PAGEVIEW (afcn VARCHAR, user VARCHAR, session VARCHAR, id VARCHAR, type VARCHAR, url VARCHAR, timestamp VARCHAR) WITH (kafka_topic='analytics_aparat_pageview', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');
CREATE STREAM ANALYTICS_APARAT_PAGEVIEW_WITH_PROPER_KEY
WITH(KAFKA_TOPIC='analytics_aparat_pageview_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
SELECT (ID + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(ID AS BIGINT) AS VIDEOID, AFCN, SESSION, TYPE, URL, TIMESTAMP
FROM ANALYTICS_APARAT_PAGEVIEW
PARTITION BY ID;
CREATE TABLE ANALYTICS_APARAT_PAGEVIEW_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_pageview_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null
GROUP BY ID, VIDEOID;
CREATE TABLE ANALYTICS_APARAT_VIDEO_PAGEVIEW_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_pageview_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null and ID != ''
GROUP BY ID, VIDEOID;CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_CLICK (afcn VARCHAR, user VARCHAR, session VARCHAR, id VARCHAR, type VARCHAR, url VARCHAR, x INT, y INT, target VARCHAR, timestamp VARCHAR) WITH (kafka_topic='analytics_aparat_recommendation_click', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');
CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_CLICK_WITH_PROPER_KEY
WITH(KAFKA_TOPIC='analytics_aparat_recommendation_click_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
SELECT (ID + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(ID AS BIGINT) AS VIDEOID, AFCN, SESSION, TYPE, URL, X, Y, TARGET, TIMESTAMP
FROM ANALYTICS_APARAT_RECOMMENDATION_CLICK
PARTITION BY ID;
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_recommendation_click_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc'
GROUP BY ID, VIDEOID;
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOSTED_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_recommendation_click_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null and TYPE = 'boosted-brc'
GROUP BY ID, VIDEOID;
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOSTED_CTA_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_recommendation_click_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null and TYPE = 'boosted-cta'
GROUP BY ID, VIDEOID;CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_RIGHT_CLICK (afcn VARCHAR, user VARCHAR, session VARCHAR, id VARCHAR, type VARCHAR, url VARCHAR, x INT, y INT, target VARCHAR, timestamp VARCHAR) WITH (kafka_topic='analytics_aparat_recommendation_right_click', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');
CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_RIGHT_CLICK_WITH_PROPER_KEY
WITH(KAFKA_TOPIC='analytics_aparat_recommendation_right_click_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
SELECT (ID + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(ID AS BIGINT) AS VIDEOID, AFCN, SESSION, TYPE, URL, X, Y, TARGET, TIMESTAMP
FROM ANALYTICS_APARAT_RECOMMENDATION_RIGHT_CLICK
PARTITION BY ID;
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_RIGHT_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_recommendation_right_click_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc'
GROUP BY ID, VIDEOID;
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOSTED_RIGHT_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_recommendation_right_click_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null and TYPE = 'boosted-brc'
GROUP BY ID, VIDEOID;
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
FROM analytics_aparat_recommendation_right_click_with_proper_key
WINDOW TUMBLING (SIZE 1 DAY)
WHERE VIDEOID is not null and TYPE = 'boosted-cta'
GROUP BY ID, VIDEOID;- KSQL Default retention is incorrect
To delete a table with it's topic:
DROP TABLE table_name DELETE TOPIC;To delete a stream with it's topic:
DROP STREAM stream_name DELETE TOPIC;To delete a topic:
docker exec -it broker kafka-topics --zookeeper zookeeper:2181 --delete --topic TOPIC_NAMETo alter a topic (e.g. for changing partitions count):
docker exec -it broker kafka-topics --zookeeper zookeeper:2181 --alter --topic TOPIC_NAME --partitions NUMBER