SELECT r.paydate AS paydate, r.orderid AS orderid, r.username AS username, r.userid AS userid, r.itemname AS itemname, r.discountname AS discountname, r.payment_gross AS payment_gross, r.payment_fee AS payment_fee, r.ordertype AS ordertype, r.provider AS provider, r.contactid AS contactid, r.pprefid AS pprefid, u.training_state AS training_state, u.training_time AS training_time, r.affiliateid AS affiliateid, IF(r.paydate >= '2021-03-31', b.affiliate_name, a.affname) AS affiliatename, SUBSTRING_INDEX(nyse_home_address,'|',-1) AS country, IF(signup.creation_date IS NOT NULL OR r.itemname LIKE '%test%drive%', 1, 0) AS is_first_payment FROM aggregate_revenue AS r LEFT OUTER JOIN users AS u ON r.userid = u.id LEFT OUTER JOIN infusionsoft_affiliates AS a ON(r.affiliateid = a.affiliateid ) LEFT OUTER JOIN tune_affiliate_name_id AS b ON(r.affiliateid = b.affiliate_id ) LEFT OUTER JOIN (SELECT userid, DATE(creation) AS creation_date FROM aggregate_revenue WHERE ordertype = 'subscr_signup' GROUP BY userid, creation_date) AS signup ON r.userid = signup.userid AND DATE(r.creation) = signup.creation_date WHERE r.contactid != 'PU9AGKW2DALPQ' AND r.paydate >= '2024-03-01' AND r.paydate < '2024-04-18' AND r.itemname LIKE '%test drive%' ORDER BY ordertype DESC

----------------------------------


SELECT 
    r.paydate AS paydate, r.orderid AS orderid, r.username AS username, r.userid AS userid, r.itemname AS itemname, r.discountname AS discountname, r.payment_gross AS payment_gross, r.payment_fee AS payment_fee, r.ordertype AS ordertype, r.provider AS provider, r.contactid AS contactid, r.pprefid AS pprefid, u.training_state AS training_state, u.training_time AS training_time, r.affiliateid AS affiliateid, IF(r.paydate >= '2021-03-31', b.affiliate_name, a.affname) AS affiliatename, SUBSTRING_INDEX(nyse_home_address,'|',-1) AS country, IF(signup.creation_date IS NOT NULL OR r.itemname LIKE '%test%drive%', 1, 0) AS is_first_payment 
FROM aggregate_revenue AS r 
LEFT OUTER JOIN users 
    AS u ON r.userid = u.id 
LEFT OUTER JOIN infusionsoft_affiliates 
    AS a ON(r.affiliateid = a.affiliateid ) 
LEFT OUTER JOIN tune_affiliate_name_id 
    AS b ON(r.affiliateid = b.affiliate_id ) 
LEFT OUTER JOIN (SELECT userid, DATE(creation) AS creation_date FROM aggregate_revenue WHERE ordertype = 'subscr_signup' GROUP BY userid, creation_date) 
    AS signup ON r.userid = signup.userid AND DATE(r.creation) = signup.creation_date 
WHERE r.contactid != 'PU9AGKW2DALPQ' AND r.paydate >= '2024-03-01' AND r.paydate < '2024-04-18' AND r.itemname LIKE '%test drive%' 
ORDER BY ordertype DESC



SELECT COUNT(*) FROM aggregate_revenue AS r INNER JOIN free_preview AS p ON r.userid = p.user_id WHERE p.scheduled_start >= '2024-04-01' AND r.paydate >= '2024-03-01' AND r.itemname = 'test drive' AND r.ordertype = 'subscr_changed';

SELECT COUNT(*) FROM free_preview WHERE scheduled_start >= '2024-04-01' AND email NOT LIKE '%mailinator%' AND email NOT LIKE '%trade-ideas%';
SELECT COUNT(*) from aggregate_revenue WHERE paydate >= '2024-03-01' AND itemname = 'test drive' AND ordertype = 'subscr_payment';

SELECT COUNT(*) FROM free_preview AS p LEFT JOIN aggregate_revenue AS r ON p.user_id = r.userid WHERE r.userid IS NULL AND p.scheduled_start > '2024-04-01';

SELECT COUNT(*) FROM free_preview AS p WHERE NOT EXISTS (SELECT 1 FROM aggregate_revenue AS r  WHERE p.user_id=r.userid AND r.paydate >= '2024-03-01' AND r.itemname = 'test drive' AND r.ordertype = 'subscr_payment') AND p.scheduled_start > '2024-04-01';