georgevanous

Untitled

Feb 10th, 2021
1,034
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # nitish_cut: youtube
  2.  
  3. SELECT
  4.     'youtube' `type`,
  5.     e.MONTH `month`, e.channel_id, revenue_share, ed.earnings gross_earnings,
  6.     ed.earnings * revenue_share / 100 AS channel_earnings,
  7.     ed.earnings * (100 - revenue_share) / 100
  8.         * IF(e.recruiter_id = e.network_id OR e.recruiter_id IS NULL OR DATE_ADD(e.date_joined, INTERVAL 1 YEAR) < e.month, 0, e.recruiter_revenue_share)
  9.         AS recruiter_earnings,
  10.     ed.earnings * (100 - revenue_share) / 100
  11.         * (1 - (IF(e.recruiter_id = e.network_id OR e.recruiter_id IS NULL OR DATE_ADD(e.date_joined, INTERVAL 1 YEAR) < e.month, 0, e.recruiter_revenue_share)))
  12.         * IF(e.network_id IS NULL, 0, e.network_revenue_share)
  13.         AS network_earnings,
  14.     ed.earnings * (100 - revenue_share) / 100
  15.         * (1 - (IF(e.recruiter_id = e.network_id OR e.recruiter_id IS NULL OR DATE_ADD(e.date_joined, INTERVAL 1 YEAR) < e.month, 0, e.recruiter_revenue_share)))
  16.         * (1 - (IF(e.network_id IS NULL, 0, e.network_revenue_share)))
  17.         AS freedom_cut,
  18.     ed.earnings * (100 - revenue_share) / 100
  19.         * (1 - (IF(e.recruiter_id = e.network_id OR e.recruiter_id IS NULL OR DATE_ADD(e.date_joined, INTERVAL 1 YEAR) < e.month, 0, e.recruiter_revenue_share)))
  20.         * (1 - (IF(e.network_id IS NULL, 0, e.network_revenue_share)))
  21.         * 0.25
  22.         AS nitish_cut
  23. -- , e.recruiter_id, e.recruiter_revenue_share
  24.  
  25. FROM earnings.base_ytpartner_earnings e
  26. INNER JOIN earnings.raw_youtube_external_data ed ON
  27.     ed.cms IN ('Garage Monkey', 'GarageMonkey') AND ed.channel_id = e.channel_id AND ed.month = e.month
  28. WHERE
  29. e.month >= '2019-05-01'
  30. ORDER BY e.month DESC
  31. LIMIT 99999;
  32.  
  33. # nitish_cut: youtube_red
  34. SELECT
  35.     'youtube_red' `type`,
  36.     e.MONTH `month`, e.channel_id, revenue_share, ed.youtubered_earnings gross_earnings,
  37.     ed.youtubered_earnings * revenue_share / 100 AS channel_earnings,
  38.     ed.youtubered_earnings * (100 - revenue_share) / 100
  39.         * IF(e.recruiter_id = e.network_id OR e.recruiter_id IS NULL OR DATE_ADD(e.date_joined, INTERVAL 1 YEAR) < e.month, 0, e.recruiter_revenue_share)
  40.         AS recruiter_earnings,
  41.     ed.youtubered_earnings * (100 - revenue_share) / 100
  42.         * (1 - (IF(e.recruiter_id = e.network_id OR e.recruiter_id IS NULL OR DATE_ADD(e.date_joined, INTERVAL 1 YEAR) < e.month, 0, e.recruiter_revenue_share)))
  43.         * IF(e.network_id IS NULL, 0, e.network_revenue_share)
  44.         AS network_earnings,
  45.     ed.youtubered_earnings * (100 - revenue_share) / 100
  46.         * (1 - (IF(e.recruiter_id = e.network_id OR e.recruiter_id IS NULL OR DATE_ADD(e.date_joined, INTERVAL 1 YEAR) < e.month, 0, e.recruiter_revenue_share)))
  47.         * (1 - (IF(e.network_id IS NULL, 0, e.network_revenue_share)))
  48.         AS freedom_cut,
  49.     ed.youtubered_earnings * (100 - revenue_share) / 100
  50.         * (1 - (IF(e.recruiter_id = e.network_id OR e.recruiter_id IS NULL OR DATE_ADD(e.date_joined, INTERVAL 1 YEAR) < e.month, 0, e.recruiter_revenue_share)))
  51.         * (1 - (IF(e.network_id IS NULL, 0, e.network_revenue_share)))
  52.         * 0.25
  53.         AS nitish_cut
  54. -- , e.recruiter_id, e.recruiter_revenue_share
  55.  
  56. FROM earnings.base_ytpartner_earnings e
  57. INNER JOIN earnings.raw_youtube_external_data ed ON
  58.     ed.cms IN ('Garage Monkey', 'GarageMonkey') AND ed.channel_id = e.channel_id AND ed.month = e.month
  59. WHERE
  60. e.month >= '2019-05-01'
  61. ORDER BY e.month DESC
  62. LIMIT 99999;
  63.  
  64. # nitish_cut: transactions
  65. SELECT
  66.     'transactional' `type`,
  67.     e.MONTH `month`, e.channel_id, revenue_share, ed.transaction_earnings gross_earnings,
  68.     0 nitish_cut
  69. -- , e.recruiter_id, e.recruiter_revenue_share
  70.  
  71. FROM earnings.base_ytpartner_earnings e
  72. INNER JOIN earnings.raw_youtube_external_data ed ON
  73.     ed.cms IN ('Garage Monkey', 'GarageMonkey') AND ed.channel_id = e.channel_id AND ed.month = e.month
  74. WHERE
  75. e.month >= '2019-05-01'
  76. ORDER BY e.month DESC
  77. LIMIT 99999;
RAW Paste Data