I would like to be able to show our partner (customer_organizations) rank by order count and then also by gross revenue week over week (month over month) etc. I want the output to show something like this. I'm currently writing this in Zoho Analytics
| Customer_Organization | Week 1 | Week 2 |
|---|---|---|
| cariloha | 1 | 3. |
| avocado | 2 | 1. |
| floyd | 3. | 2. |
I originally had DATEADD - 30 in this but I can't figure out how to get week over week, month over month, etc.
SELECT
customer_organizations.id,
customer_organizations.name,
COUNT(orders.id),
RANK() OVER (ORDER BY COUNT(orders.id) DESC) Rank
FROM
orders
JOIN
customer_organizations ON orders.customer_organization_id = customer_organizations.id
WHERE
customer_organizations.status = 'enabled'
AND customer_organization_type = 'partner'
GROUP BY
customer_organizations.id,
customer_organizations.name
ORDER BY
rank;
