I'm doing some work around what we're spending on support vs. how much those users bring in and came into this unique problem.
Tables I have:
- Revenue table: A row for each time a user generates revenue on the platform
- Support Contacts Table: A row for each time a user contacts support and the cost associated with that contact
I'm trying to get a table at a daily grain that details...
- How many users contacted support on the given day
- How much revenue did all users bring in in the last 30 days?
- How much did we spend on support contacts in the last 30 days?
- The tough part: How much did the users who contacted support on the given day bring in in the last 30 days?
Here's what I have so far:
SELECT DISTINCT
-- Revenue generation date
r.revenue_date
-- Easy summing of contacts/revenue/costs on the given day
,COUNT(DISTINCT sc.user_pk) AS num_user_contacting_support_on_day
,SUM(r.revenue) AS all_users_revenue_for_day
,SUM(sc.support_contact_cost) AS support_costs_on_day
-- Double check that this would sum the p30d revenue/costs for the given day?
,SUM(IF(r.revenue_date BETWEEN r.revenue_date AND DATE_SUB(r.revenue_date, INTERVAL 30 DAY), c.revenue, NULL)) AS p30d_revenue_all_users
,SUM(IF(sc.support_contact_date BETWEEN r.revenue_date AND DATE_SUB(r.revenue_date, INTERVAL 30 DAY), sc.support_contact_cost, NULL)) AS p30d_support_contact_cost
-- The tough part:
-- How do I get the revenue for ONLY users who contacted support on the given day?
-- How do I get the p30d revenue for ONLY users who contacted support on the given day?
FROM revenue_table r
LEFT JOIN support_contact_table sc
ON r.revenue_date = sc.support_contact_date
GROUP BY r.revenue_date
