API tools faq
paste
Advertisement
borisch

Untitled

borisch
May 20th, 2025
493
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 10.12 KB | None | 0 0
raw download clone embed print report
  1. -- sberkot_post_stats
  2. {{ config(order_by='(-Index)', engine='MergeTree()', materialized='table') }}
  3. SELECT
  4.     toInt32(PostNumber) AS INDEX,
  5.     concat('№', PostNumber, ': ', PostName) AS Post,
  6.     Recipeints,
  7.     Openers,
  8.     Completors,
  9.     LinkClickers,
  10.     OpensRatio,
  11.     CompletionsRatio,
  12.     ClicksRatio
  13. FROM
  14. (
  15.     SELECT
  16.         PostNumber,
  17.         PostRecipients AS RecipeintsWithoutHistoricalData,
  18.         multiIf(PostNumber = '1', 13739359, PostNumber = '2', 13771047, PostNumber = '3', 13771050, PostNumber = '4', 13776502, PostNumber = '5', 13769429, PostNumber = '6', 13990866, PostNumber = '7', 13988562, PostNumber = '8', 13906311, PostNumber = '9', 13860743, PostNumber = '10', 13785997, PostNumber = '11', 13738768, PostNumber = '12', 13853044, PostNumber = '13', 13804048, PostNumber = '14', 13852410, PostNumber = '15', 13888531, PostNumber = '16', 14317787, PostNumber = '17', 14261720, PostNumber = '18', 14258771, PostNumber = '19', 14240575, PostNumber = '20', 14191218, PostNumber = '21', 14141233, PostNumber = '22', 14090904, PostNumber = '23', 14015485, PostNumber = '24', 14027943, PostNumber = '25', 14054771, RecipeintsWithoutHistoricalData) AS Recipeints,
  19.         PostOpeners AS OpenersWithoutHistoricalData,
  20.         multiIf(PostNumber = '1', 6141500, PostNumber = '2', 6832000, PostNumber = '3', 6466400, PostNumber = '4', 6707200, PostNumber = '5', 6563000, PostNumber = '6', 6457000, PostNumber = '7', 6203900, PostNumber = '8', 6430600, PostNumber = '9', 6627000, PostNumber = '10', 6242800, PostNumber = '11', 6493000, PostNumber = '12', 6355700, PostNumber = '13', 6382500, PostNumber = '14', 6387200, PostNumber = '15', 6494900, PostNumber = '16', 4601800, PostNumber = '17', 6645500, PostNumber = '18', 6346900, PostNumber = '19', 7221000, PostNumber = '20', 6542200, PostNumber = '21', 6448300, PostNumber = '22', 6035800, PostNumber = '23', 6118600, PostNumber = '24', 6461100, PostNumber = '25', 6641400, OpenersWithoutHistoricalData) AS Openers,
  21.         PostCompletors AS CompletorsWithoutHistoricalData,
  22.         multiIf(PostNumber = '1', 2883296, PostNumber = '2', 2021800, PostNumber = '2', 2021800, PostNumber = '2', 2021800, PostNumber = '3', 1687938, PostNumber = '4', 1736441, PostNumber = '5', 1721056, PostNumber = '5', 1721056, PostNumber = '5', 1721056, PostNumber = '5', 1721056, PostNumber = '5', 1721056, PostNumber = '6', 1680661, PostNumber = '7', 1067911, PostNumber = '7', 1067911, PostNumber = '7', 1067911, PostNumber = '7', 1067911, PostNumber = '8', 1366227, PostNumber = '9', 1366162, PostNumber = '10', 1054740, PostNumber = '10', 1054740, PostNumber = '11', 1109628, PostNumber = '11', 1109628, PostNumber = '12', 1483197, PostNumber = '13', 1190480, PostNumber = '14', 1204215, PostNumber = '15', 1023904, PostNumber = '15', 1023904, PostNumber = '15', 1023904, PostNumber = '16', 1129101, PostNumber = '16', 1129101, PostNumber = '17', 1139899, PostNumber = '17', 1139899, PostNumber = '18', 1409460, PostNumber = '18', 1409460, PostNumber = '19', 0, PostNumber = '20', 2400131, PostNumber = '21', 824647, PostNumber = '21', 824647, PostNumber = '21', 824647, PostNumber = '22', 914102, PostNumber = '22', 914102, PostNumber = '23', 1436749, PostNumber = '24', 929147, PostNumber = '24', 929147, PostNumber = '25', 1055327, PostNumber = '25', 1055327, CompletorsWithoutHistoricalData) AS Completors,
  23.         PostLinkClickers AS LinkClickersWithoutHistoricalData,
  24.         multiIf(PostNumber = '1', 217248, PostNumber = '2', 70585, PostNumber = '3', 110187, PostNumber = '4', 131778, PostNumber = '5', (((5737 + 13598) + 2715) + 3532) + 4689, PostNumber = '6', 234081, PostNumber = '7', ((11034 + 1) + 8407) + 17025, PostNumber = '8', 5377, PostNumber = '9', 43485, PostNumber = '10', 135673 + 6930, PostNumber = '11', 18713 + 19977, PostNumber = '12', 187124, PostNumber = '13', 48895, PostNumber = '14', 4, PostNumber = '15', (0 + 24385) + 41085, PostNumber = '16', 8901, PostNumber = '17', 187124, PostNumber = '18', 10321, PostNumber = '19', 30490 + 17279, PostNumber = '20', 107100, PostNumber = '21', 187124, PostNumber = '22', 11157 + 14754, PostNumber = '23', 25235, PostNumber = '24', 0, PostNumber = '25', 5743, PostNumber = '43', 17623, LinkClickersWithoutHistoricalData) AS LinkClickers,
  25.         Openers / Recipeints AS OpensRatio,
  26.         Completors / Recipeints AS CompletionsRatio,
  27.         LinkClickers / Recipeints AS ClicksRatio
  28.     FROM
  29.     (
  30.         SELECT
  31.             PostNumber,
  32.             sumIf(Users, Event = 'postSend') AS PostRecipients,
  33.             sumIf(Users, Event = 'postOpen') AS PostOpeners,
  34.             sumIf(Users, Event = 'postComplete') AS PostCompletors,
  35.             sumIf(Users, Event = 'mtLinkClick') AS PostLinkClickers
  36.         FROM
  37.         (
  38.             SELECT
  39.                 PostNumber,
  40.                 Event,
  41.                 uniq(MAUserId) AS Users
  42.             FROM {{ REF('sberkot') }}
  43.             WHERE (Event IN ('postSend', 'postOpen', 'postComplete', 'mtLinkClick'))
  44.                 AND (MyTrackerAppId IN ('59040', '76264'))
  45.                 AND (PostNumber != '')
  46.                 AND (Interactive = '1')
  47.                 AND (Targeting = '')
  48.             GROUP BY
  49.                 PostNumber,
  50.                 Event
  51.         )
  52.         GROUP BY
  53.             PostNumber
  54.     )
  55.     ORDER BY toInt32(PostNumber) DESC
  56. ) AS stats
  57. LEFT JOIN
  58. (
  59.     SELECT
  60.         PostNumber,
  61.         anyHeavy(PostName) AS PostName
  62.     FROM {{ REF('sberkot') }}
  63.     WHERE (Event = 'postSend')
  64.         AND (Interactive = '1')
  65.         AND (Targeting = '')
  66.     GROUP BY PostNumber
  67. ) AS pn ON stats.PostNumber = pn.PostNumber
  68.  
  69.  
  70. -- sberkot_targeted_post_stats
  71. {{ config(order_by='(-Index)', engine='MergeTree()', materialized='table') }}
  72. SELECT
  73.     toInt32(PostNumber) AS INDEX,
  74.     concat('№', PostNumber, ': ', PostName) AS Post,
  75.     Targeting,
  76.     Recipients,
  77.     Openers,
  78.     Completors,
  79.     LinkClickers,
  80.     OpensRatio,
  81.     CompletionsRatio,
  82.     ClicksRatio
  83. FROM
  84. (
  85.     SELECT
  86.         PostNumber,
  87.         Targeting,
  88.         Recipients,
  89.         Openers,
  90.         Completors,
  91.         LinkClickers,
  92.         Openers / Recipients AS OpensRatio,
  93.         Completors / Recipients AS CompletionsRatio,
  94.         LinkClickers / Recipients AS ClicksRatio
  95.     FROM
  96.     (
  97.         SELECT
  98.             PostNumber,
  99.             Targeting,
  100.             sumIf(Users, Event = 'postSend') AS Recipients,
  101.             sumIf(Users, Event = 'postOpen') AS Openers,
  102.             sumIf(Users, Event = 'postComplete') AS Completors,
  103.             sumIf(Users, Event = 'mtLinkClick') AS LinkClickers
  104.         FROM
  105.         (
  106.             SELECT
  107.                 PostNumber,
  108.                 -- clean up targeting naming mismatches
  109.                 multiIf(
  110.                     Targeting = 'users14_24' , 'users_14-24',
  111.                     Targeting = 'users35+' , 'users_35+',
  112.                     Targeting = 'users18+' , 'users_18+',
  113.                     Targeting = 'users14_24_35+' , 'users_14-24, 35+',
  114.                     Targeting = 'users30+' , 'users_30+',
  115.                     PostNumber = '53' AND Targeting = 'users_clickers_campaign43', 'users_14-17_dota',
  116.                     PostNumber = '54' AND Targeting IN ('users_14_25', 'users_35_plus'), 'users_14_25_35_plus',
  117.                     Targeting = 'users_14_22_', 'users_14_22_&_parents',
  118.                     PostNumber = '59' AND Targeting = 'users_14_25_35', 'users_14_25_&_35+',
  119.                     Targeting = 'users_14_25_', 'users_14_25_&_35+',
  120.                     Targeting
  121.                 ) AS Targeting,
  122.                 Event,
  123.                 uniq(MAUserId) AS Users
  124.             FROM {{ REF('sberkot') }}
  125.             WHERE (Event IN ('postSend', 'postOpen', 'postComplete', 'mtLinkClick'))
  126.                 AND (MyTrackerAppId IN ('59040', '76264'))
  127.                 AND (PostNumber != '')
  128.                 AND (Interactive = '1')
  129.                 AND (Targeting != '')
  130.             GROUP BY
  131.                 PostNumber,
  132.                 Targeting,
  133.                 Event
  134.         )
  135.         GROUP BY
  136.             PostNumber,
  137.             Targeting
  138.     )
  139.     ORDER BY toInt32(PostNumber) DESC
  140. ) AS stats
  141. LEFT JOIN
  142. (
  143.     SELECT
  144.         PostNumber,
  145.         anyHeavy(PostName) AS PostName
  146.     FROM {{ REF('sberkot') }}
  147.     WHERE (Event = 'postSend')
  148.         AND (Interactive = '1')
  149.         AND (Targeting != '')
  150.     GROUP BY PostNumber
  151. ) AS pn ON stats.PostNumber = pn.PostNumber
  152. WHERE Recipients != 0
  153.  
  154.  
  155. --sberkot_noninteractive_posts_stats
  156. {{ config(order_by='(-Index)', engine='MergeTree()', materialized='table') }}
  157. SELECT
  158.     toInt32OrZero(PostNumber) AS INDEX,
  159.     concat('№', PostNumber, ': ', PostName) AS Post,
  160.     Recipeints,
  161.     Openers,
  162.     LinkClickers,
  163.     OpensRatio,
  164.     ClicksRatio
  165. FROM
  166. (
  167.     SELECT
  168.         PostNumber,
  169.         multiIf(PostNumber = '7', 20133, 0) AS HardcodeClicks,
  170.         PostRecipients AS Recipeints,
  171.         PostOpeners AS Openers,
  172.         PostLinkClickers + HardcodeClicks AS LinkClickers,
  173.         Openers / Recipeints AS OpensRatio,
  174.         LinkClickers / Recipeints AS ClicksRatio
  175.     FROM
  176.     (
  177.         SELECT
  178.             PostNumber,
  179.             Interactive,
  180.             sumIf(Users, Event = 'postSend') AS PostRecipients,
  181.             sumIf(Users, Event = 'postOpen') AS PostOpeners,
  182.             sumIf(Users, Event = 'mtLinkClick') AS PostLinkClickers
  183.         FROM
  184.         (
  185.             SELECT
  186.                 PostNumber,
  187.                 Interactive,
  188.                 Event,
  189.                 uniq(MAUserId) AS Users
  190.             FROM {{ REF('sberkot') }}
  191.             WHERE (Event IN ('postSend', 'postOpen', 'postComplete', 'mtLinkClick')) AND (MyTrackerAppId IN ('59040', '76264')) AND (Interactive != '1') AND (PostNumber != '')
  192.             GROUP BY
  193.                 PostNumber,
  194.                 Interactive,
  195.                 Event
  196.         )
  197.         GROUP BY
  198.             PostNumber,
  199.             Interactive
  200.     )
  201.     ORDER BY toInt32OrZero(PostNumber) DESC
  202. ) AS stats
  203. LEFT JOIN
  204. (
  205.     SELECT
  206.         PostNumber,
  207.         anyHeavy(PostName) AS PostName
  208.     FROM {{ REF('sberkot') }}
  209.     WHERE (Event = 'postSend') AND (Interactive != '1')
  210.     GROUP BY PostNumber
  211. ) AS pn ON stats.PostNumber = pn.PostNumber
  212.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!