With this schema, I am trying to get the total revenue (SUM(total_amt_usd)) from the orders table but grouped by the channels in web_events table.
I have tried the query below but my result is wrong.
SELECT w.channel AS channels,
SUM(o.total_amt_usd) AS total_revenue
FROM orders o
JOIN accounts a
ON o.account_id = a.id
JOIN web_events w
ON w.account_id = a.id
GROUP BY 1
ORDER BY 2 DESC
I know this is wrong because my total revenue from the orders table is $23million.
Please how best can I do this ?