Calculate difference in hours/days between different actions by a same user sql

Question

I have a table where users perform an order action. I want to get difference in dates between his two or more orders. And similar for all users and then calculate their average or median.

Another issue is the order rows are duplicates because of another column in the table called order_received time which are 5 secs apart due to this two rows are created for the same users with same order time.

The average interval between orders, for one customer, then is just the interval between the first order and the last order, divided by the number of orders. Then just calculate the average or median of that across all customers? — MatBailie, Mar 21 '19 at 16:12
What did you try and what was the issue with such a trial? SO is meant to help earnest coding efforts that yield errors or undesired results. Right now this question is too broad. — Parfait, Mar 21 '19 at 16:22
Found partial answer here. Was trying to do something similar https://stackoverflow.com/questions/9994862/date-difference-between-consecutive-rows — dj17, Mar 21 '19 at 16:42
@dj17 Provide some sample table data and the result expected. — Mark Barinstein, Mar 21 '19 at 17:40

Gro · Answer 1 · 2019-03-22T00:04:53.060

Based on your comment on my initial answer here is another worksheet.

Table DDL

create table tbl_order(
  order_id integer,
  account_number integer,
  ordered_at date
);

Data as in other thread you pointed out

insert into tbl_order values (1, 1001, to_date('10-Sep-2019 00:00:00', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (2, 2001, to_date('01-Sep-2019 00:00:00', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (3, 2001, to_date('03-Sep-2019 00:00:00', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (4, 1001, to_date('12-Sep-2019 00:00:00', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (5, 3001, to_date('18-Sep-2019 00:00:00', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (6, 1001, to_date('20-Sep-2019 00:00:00', 'DD-MON-YYYY HH24:MI:SS'));

Query

WITH VW AS (
  SELECT ACCOUNT_NUMBER, 
    MIN(ORDERED_AT) EARLIEST_ORDER_AT, 
    MAX(ORDERED_AT) LATEST_ORDER_AT, 
    ROUND(MAX(ORDERED_AT) - MIN(ORDERED_AT), 5)  DIFF_IN_DAYS,
    COUNT(*) TOTAL_ORDER_COUNT
  FROM TBL_ORDER
  GROUP BY ACCOUNT_NUMBER
)
SELECT ACCOUNT_NUMBER, EARLIEST_ORDER_AT, LATEST_ORDER_AT, 
  DIFF_IN_DAYS, ROUND( DIFF_IN_DAYS/TOTAL_ORDER_COUNT, 4) AVERAGE 
FROM VW;

Result

===========Initial answer hereafter===========

Your question is not entirely clear, for example

Do you want difference in date per day (a user can make multiple orders per day) or just between their earliest and latest orders
What do you mean by average is it just (latest order date - earliest order date) / total purchase? This will be hours / purchase. is it even useful?

Anyways, here is a working sheet, this will give enough to set you in right direction (hopefully). This is for Oracle database, will work mostly for other database except the time conversion functions used here. You will have to search and use equivalent functions for database of your choice, if its not Oracle.

Create table

create table tbl_order(
  order_id integer,
  user_id integer,
  item varchar2(100),
  ordered_at date
);

Insert some data

insert into tbl_order values (8, 1, 'A2Z', to_date('21-Mar-2019 16:30:20', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (1, 1, 'ABC', to_date('22-Mar-2019 07:30:20', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (2, 1, 'ABC', to_date('22-Mar-2019 07:30:20', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (3, 1, 'EFGT', to_date('22-Mar-2019 09:30:30', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (4, 1, 'XYZ', to_date('22-Mar-2019 12:38:50', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (5, 1, 'ABC', to_date('22-Mar-2019 16:30:20', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (6, 2, 'ABC', to_date('22-Mar-2019 14:20:20', 'DD-MON-YYYY HH24:MI:SS'));
insert into tbl_order values (7, 2, 'A2C', to_date('22-Mar-2019 14:20:50', 'DD-MON-YYYY HH24:MI:SS'));

Get latest, earliest and total_purchase per user and an average

WITH VW AS (
  SELECT USER_ID, 
  TO_CHAR(MIN(ORDERED_AT), 'DD-MON-YYYY HH24:MI:SS') EARLIEST_ORDER_AT, 
  TO_CHAR(MAX(ORDERED_AT), 'DD-MON-YYYY HH24:MI:SS')LATEST_ORDER_AT, 
  ROUND(MAX(ORDERED_AT) - MIN(ORDERED_AT), 5) * 24 DIFF_IN_HOURS,
  COUNT(*) TOTAL_ORDER_COUNT
  FROM TBL_ORDER
  GROUP BY USER_ID
)
SELECT USER_ID, EARLIEST_ORDER_AT, LATEST_ORDER_AT, 
  DIFF_IN_HOURS,  DIFF_IN_HOURS/TOTAL_ORDER_COUNT AVERAGE 
FROM VW;

Get latest, earliest and total_purchase per user per day and an average

WITH VW AS (
  SELECT USER_ID, TO_CHAR(ORDERED_AT, 'DD-MON-YYYY') ORDER_DATE_PART, 
  TO_CHAR(MIN(ORDERED_AT), 'DD-MON-YYYY HH24:MI:SS') EARLIEST_ORDER_AT, 
  TO_CHAR(MAX(ORDERED_AT), 'DD-MON-YYYY HH24:MI:SS')LATEST_ORDER_AT, 
  ROUND(MAX(ORDERED_AT) - MIN(ORDERED_AT), 5) * 24 DIFF_IN_HOURS,
  COUNT(*) TOTAL_ORDER_COUNT
  FROM TBL_ORDER
  GROUP BY USER_ID, TO_CHAR(ORDERED_AT, 'DD-MON-YYYY')
)
SELECT USER_ID, ORDER_DATE_PART, EARLIEST_ORDER_AT, LATEST_ORDER_AT, 
  DIFF_IN_HOURS,  DIFF_IN_HOURS/TOTAL_ORDER_COUNT AVERAGE 
FROM VW;

Hi Gro, Thanks for the answer. However i'm trying to do something different. My data is same as in this question https://stackoverflow.com/questions/9994862/date-difference-between-consecutive-rows However, in the solution there. I want just single row per account number and then the avg of days between their dates. Adding avg(Daysdiff) is showing error. — dj17, Mar 21 '19 at 22:54
Based on your additional clarity, I have added another section in my answer. See if it meets your need. — Gro, Mar 22 '19 at 13:19

Calculate difference in hours/days between different actions by a same user sql

1 Answers1