the dataset of subquery is here
id itm_id paid_at ord_r total_r
17 3266 2013-05-25 08:27:17 1 3
17 3219 2013-05-25 08:27:17 2 3
17 3964 2013-05-25 08:27:17 3 3
25 2105 2013-05-17 03:11:48 1 2
25 1376 2013-05-17 03:11:48 2 2
63 2140 2013-07-07 11:26:45 1 3
the code is here
for find out the average difference in order time, BUt i looked up here, and i found this piece of code
But i didn't understand why i use (toatl-1)
if someone kindly explain the process
i am doing this on mode analytics i don't know which machine it use, whether mysql or sqlserver
SELECT
user_id,
item_id,
CASE WHEN total_order-1 > 0
THEN datediff(day, max(paid_at), min(paid_at))/ (total_order-1)
ELSE datediff(day, max(paid_at), min(paid_at)) END AS avg_time
FROM
(SELECT
user_id,
item_id,
paid_at,
ROW_NUMBER( ) OVER (PARTITION BY user_id ORDER by paid_at ASC) AS order_rank,
COUNT(item_id) OVER(PARTITION BY user_id ORDER BY paid_at ASC) AS total_order
from
dsv1069.orders) user_level
But the problems is
ERROR: column "day" does not exist
