While writing SQL queries, sometimes we need to use RANK function with GROUP BY.
But the current version of Metabase doesn't support it.
So, here is the alternative of RANK function.
Example:
Suppose you have a table which consists of User IDs and Order Date. There may be multiple orders by a customer. So, there will be multiple Order Dates for a single User ID.
Sample Input -
user_id order_date
1 '2022-07-15'
2 '2022-07-18'
2 '2022-06-13'
1 '2022-08-25'
2 '2022-10-12'
Now, suppose you want to give a rank to every order for every customer based on the order date.
Output:
user_id order_date order_number
1 '2022-07-15' 1
2 '2022-07-18' 2
2 '2022-06-13' 1
1 '2022-08-25' 2
2 '2022-10-12' 3
To do this, MySQL has this simple syntax :
SELECT user_id, order_date, rank()
OVER ( partition by user_id order by order_date ) AS 'order_number' FROM tblorders
But the current version of Metabase doesn't support this.
So, you can use this syntax :
SELECT user_id, order_date,
(SELECT COUNT(*) FROM tblorders tod2 WHERE tod1.user_id = tod2.user_id AND
tod1.user_id >= tod2.user_id) AS 'order_number'
FROM tblorders tod1
It will return the desired result.
You can sort on basis of user_id as per your requirement.
Thanks !!