Bdtjtk

There is a hypothetical database that records millions of customer transactions per day.

Aim is to identify unique patterns in the transactions i.e. count unique orders in relative time frames per second, minute, per hour, etc. as well as unique orders per locations.

There are a number of other variables but I have provided this extract to keep it simple.

So, basically, I would like to count in relative time frames rather than the 'cardinal' time extract. I'm not sure how to do that. I have searched the different forums but can't work out how to do this in Teradata sql.

I would really appreciate any help with this!

Thanks in advance

Example dataset

+----------+---------------+----------------------+------+

| Customer |    Product    |       DateTime       | City |

+----------+---------------+----------------------+------+

|        1 | Fortnite      | 2018-10-29 11:18:54  | AL   |

|        1 | PUBG          | 2018-10-29 11:19:42  | AK   |

|        1 | Overwatch     | 2018-10-29 11:19:42  | AZ   |

|        1 | DoTA2         | 2018-10-29 11:19:42  | AR   |

|        1 | CS:GO         | 2018-10-29 11:19:43  | CA   |

|        1 | Rocket league | 2018-10-29 11:19:44  | CO   |

|        1 | PUBG          | 2018-10-29 11:19:46  | AR   |

|        1 | Borderlands2  | 2018-10-29 11:19:46  | CT   |

|        1 | CS:GO         | 2018-10-29 11:19:47  | CA   |

|        1 | Borderlands2  | 2018-10-29 11:19:47. | CT   |

|        1 | Path of exile | 2018-10-29 11:19:51  | CT   |

|        1 | Path of exile | 2018-10-29 11:19:53  | CT   |

|        1 | Hearthstone   | 2018-10-29 11:19:56  | CT   |

|        1 | Hearthstone   | 2018-10-29 11:19:57  | DE   |

|        1 | Wonderputt    | 2018-10-29 11:19:57  | CT   |

+----------+---------------+----------------------+------+

I used this code and got the answer below

SELECT  Customer, Product, DateTime, City



    ,COUNT(*) OVER (PARTITION BY Customer, DateTime + INTERVAL '1' SECOND) AS #ofOrders_per_1sec





    ,DENSE_RANK() OVER (PARTITION BY Customer,

        EXTRACT(DAY FROM DateTime), 

        EXTRACT(HOUR FROM DateTime), 

        EXTRACT(MINUTE FROM DateTime), 

        EXTRACT(SECOND FROM DateTime + INTERVAL '1' SECOND) 

        ORDER BY Product ASC) 

    + DENSE_RANK() OVER (PARTITION BY Customer,

        EXTRACT(DAY FROM DateTime), 

        EXTRACT(HOUR FROM DateTime),

        EXTRACT(MINUTE FROM DateTime),

        EXTRACT(MINUTE FROM DateTime),

        EXTRACT(SECOND FROM DateTime + INTERVAL '1' SECOND) ORDER BY Product DESC) - 1 

        AS Unique_Products_per_1sec



        ,DENSE_RANK() OVER (PARTITION BY Customer,

        EXTRACT(DAY FROM DateTime), 

        EXTRACT(HOUR FROM DateTime), 

        EXTRACT(MINUTE FROM DateTime + INTERVAL '1' MINUTE) 

        ORDER BY Product ASC) 

    + DENSE_RANK() OVER (PARTITION BY Customer,

        EXTRACT(DAY FROM DateTime), 

        EXTRACT(HOUR FROM DateTime),

        EXTRACT(MINUTE FROM DateTime),

        EXTRACT(MINUTE FROM DateTime + INTERVAL '1' MINUTE) ORDER BY Product DESC) - 1 

        AS Unique_Products_per_1min 



        ,DENSE_RANK() OVER (PARTITION BY Customer,

        EXTRACT(DAY FROM DateTime), 

        EXTRACT(HOUR FROM DateTime), 

        EXTRACT(MINUTE FROM DateTime), 

        EXTRACT(SECOND FROM DateTime + INTERVAL '1' SECOND) 

        ORDER BY City ASC) 

    + DENSE_RANK() OVER (PARTITION BY Customer,

        EXTRACT(DAY FROM DateTime), 

        EXTRACT(HOUR FROM DateTime),

        EXTRACT(MINUTE FROM DateTime),

        EXTRACT(MINUTE FROM DateTime),

        EXTRACT(SECOND FROM DateTime + INTERVAL '1' SECOND) ORDER BY City DESC) - 1 

        AS Unique_City_per_1sec



    ,DENSE_RANK() OVER (PARTITION BY Customer,

        EXTRACT(DAY FROM DateTime), 

        EXTRACT(HOUR FROM DateTime), 

        EXTRACT(MINUTE FROM DateTime + INTERVAL '1' MINUTE) 

        ORDER BY City ASC) 

    + DENSE_RANK() OVER (PARTITION BY Customer,

        EXTRACT(DAY FROM DateTime), 

        EXTRACT(HOUR FROM DateTime),

        EXTRACT(MINUTE FROM DateTime),

        EXTRACT(MINUTE FROM DateTime + INTERVAL '1' MINUTE) ORDER BY City DESC) - 1 

        AS Unique_City_per_1min

FROM Dataset

Result from the code above
Current result output

However, I would expect the results below:
Ideally, to count the transactions in relative terms instead of using 'cardinal' or absolute time xtract from the datetime field.

Desired result output
Desired result output