I am using MariaDB 10.2.4 in MySQL syntax and attempting to count the number of transactions that have occurred in the previous 24 hours based on the time stamp (not the calendar day) of the row for each customer for each transaction.
Column Information:
- row_number: I added this for ease of reading its not actually present
- order_no: is a unique value for each row
- customer_id: is unique by customer but repeated in the table
- order_date: is a timestamp that the count must be performed on
- dollar_value: is there for context and is a decimal
Here is a manually made table to show what my desired output will be. The input will be all the same columns, excluding the output (trans_count) and row_number column.
Examples:
Row 3. The time frame I'm interested in is 2022-02-11 22:53:50 (the value in the order_date column) to 2022-02-10 22:53:50 (order_date - interval 1 day) by customer ID 1111171. Row 4 and 6 match this, so the output for row 3 is 3 (including the row 3).
Row 4. The time frame is 2022-02-11 06:49:36 to 2022-02-10 06:49:36 for customer 1111171. Row 6,8,9,10 fall within the timeframe and are the same customer_id, so the trans_count value is 5.
Table
| row_number | order_no | customer_id | order_date | dollar_value | trans_count |
|---|---|---|---|---|---|
| 1 | 8888883 | 1111100 | 2022-02-14 01:10:04 | 2256.0 | 1 |
| 2 | 8888837 | 1111100 | 2022-02-12 05:46:32 | 1457.2 | 1 |
| 3 | 8888812 | 1111171 | 2022-02-11 22:53:50 | 1757.2 | 3 |
| 4 | 8888887 | 1111171 | 2022-02-11 06:49:36 | 1350.2 | 5 |
| 5 | 8888804 | 1111100 | 2022-02-11 03:10:07 | 1853.6 | 1 |
| 6 | 8888866 | 1111171 | 2022-02-11 01:20:26 | 1053.0 | 4 |
| 7 | 8888833 | 1111181 | 2022-02-10 21:09:05 | 253.2 | 1 |
| 8 | 8888874 | 1111171 | 2022-02-10 18:06:55 | 1958.6 | 3 |
| 9 | 8888829 | 1111171 | 2022-02-10 10:11:59 | 1456.2 | 2 |
| 10 | 8888802 | 1111171 | 2022-02-10 09:55:31 | 956.6 | 1 |
| 11 | 8888835 | 1111100 | 2022-02-09 19:40:24 | 756.4 | 2 |
| 12 | 8888810 | 1111123 | 2022-02-09 01:34:56 | 3179.5 | 1 |
| 13 | 8888850 | 1111100 | 2022-02-08 20:00:20 | 629.0 | 1 |
| 14 | 8888821 | 1111171 | 2022-02-08 17:59:05 | 1249.45 | 2 |
| 15 | 8888809 | 1111171 | 2022-02-08 06:25:15 | 1250.0 | 1 |
| 16 | 8888837 | 1111147 | 2022-02-08 06:18:15 | 184.6 | 1 |
| 17 | 8888836 | 1111171 | 2022-02-07 12:01:47 | 88.28 | 1 |
| 18 | 8888808 | 1111147 | 2022-02-05 12:02:49 | 3008.7 | 3 |
| 19 | 8888890 | 1111147 | 2022-02-05 11:48:16 | 1543.31 | 2 |
| 20 | 8888805 | 1111147 | 2022-02-05 11:37:55 | 2617.4 | 1 |
I intend to also apply some other filters, such as only counting dollar_value above 1000, but I expect that shouldn't affect the logic of the count.
I have been able to do this in PySpark, but I couldn't repeat the same logic. Here is a snippet from a function I have incorporated it into.
'''
# Function to calculate number of seconds from number of days
days = lambda i: i * 86400
# Create window by casting timestamp to long (number of seconds) then defining the number of days you wish to review
w = (Window.partitionBy('customer_id').orderBy(F.col(date_column).cast('long')).rangeBetween(-days(monitor_length_days), 0))
# Add total value of transactions undertaken within time frame by customer_id
filteredDF = filteredDF.withColumn(output_name, F.count('customer_id').over(w))
'''
Please let me know if you have any questions,