# nitish_cut: youtube

SELECT
    'youtube' `type`,
    e.MONTH `month`, e.channel_id, revenue_share, ed.earnings gross_earnings,
    ed.earnings * revenue_share / 100 AS channel_earnings,
    ed.earnings * (100 - revenue_share) / 100
        * 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)
        AS recruiter_earnings,
    ed.earnings * (100 - revenue_share) / 100
        * (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)))
        * IF(e.network_id IS NULL, 0, e.network_revenue_share)
        AS network_earnings,
    ed.earnings * (100 - revenue_share) / 100
        * (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)))
        * (1 - (IF(e.network_id IS NULL, 0, e.network_revenue_share)))
        AS freedom_cut,
    ed.earnings * (100 - revenue_share) / 100
        * (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)))
        * (1 - (IF(e.network_id IS NULL, 0, e.network_revenue_share)))
        * 0.25
        AS nitish_cut
-- , e.recruiter_id, e.recruiter_revenue_share

FROM earnings.base_ytpartner_earnings e
INNER JOIN earnings.raw_youtube_external_data ed ON
    ed.cms IN ('Garage Monkey', 'GarageMonkey') AND ed.channel_id = e.channel_id AND ed.month = e.month
WHERE
e.month >= '2019-05-01'
ORDER BY e.month DESC
LIMIT 99999;

# nitish_cut: youtube_red
SELECT
    'youtube_red' `type`,
    e.MONTH `month`, e.channel_id, revenue_share, ed.youtubered_earnings gross_earnings,
    ed.youtubered_earnings * revenue_share / 100 AS channel_earnings,
    ed.youtubered_earnings * (100 - revenue_share) / 100
        * 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)
        AS recruiter_earnings,
    ed.youtubered_earnings * (100 - revenue_share) / 100
        * (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)))
        * IF(e.network_id IS NULL, 0, e.network_revenue_share)
        AS network_earnings,
    ed.youtubered_earnings * (100 - revenue_share) / 100
        * (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)))
        * (1 - (IF(e.network_id IS NULL, 0, e.network_revenue_share)))
        AS freedom_cut,
    ed.youtubered_earnings * (100 - revenue_share) / 100
        * (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)))
        * (1 - (IF(e.network_id IS NULL, 0, e.network_revenue_share)))
        * 0.25
        AS nitish_cut
-- , e.recruiter_id, e.recruiter_revenue_share

FROM earnings.base_ytpartner_earnings e
INNER JOIN earnings.raw_youtube_external_data ed ON
    ed.cms IN ('Garage Monkey', 'GarageMonkey') AND ed.channel_id = e.channel_id AND ed.month = e.month
WHERE
e.month >= '2019-05-01'
ORDER BY e.month DESC
LIMIT 99999;

# nitish_cut: transactions
SELECT
    'transactional' `type`,
    e.MONTH `month`, e.channel_id, revenue_share, ed.transaction_earnings gross_earnings,
    0 nitish_cut
-- , e.recruiter_id, e.recruiter_revenue_share

FROM earnings.base_ytpartner_earnings e
INNER JOIN earnings.raw_youtube_external_data ed ON
    ed.cms IN ('Garage Monkey', 'GarageMonkey') AND ed.channel_id = e.channel_id AND ed.month = e.month
WHERE
e.month >= '2019-05-01'
ORDER BY e.month DESC
LIMIT 99999;