Tracking purchases by time of day

I'm trying to track purchasing patterns based on the time of the purchase. I have 3 periods DAY 07:15:00 - 15:14:59.999999, SWING 15:15:00 - 23:14:59.999999 and GRAVE 23:15:00 - 07:14:59.999999. I believe I can use extract() the time out but I'm unsure how to handle the fractional part of the seconds. In addition, I'm not sure how to figure out what PERIOD should be INCREMENTED. I was hoping someone could help me out.

Below is my setup, test CASE and expected results. Any help would be greatly appreciated.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Aaron' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Jones' FROM DUAL UNION ALL
SELECT 3, 'Roz', 'Doyle' FROM DUAL;

create table purchases(
      ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
      customer_id   number,
      PRODUCT_ID NUMBER,
      QUANTITY NUMBER,
      purchase_date timestamp
    );

insert  into purchases (customer_id, product_id, quantity, purchase_date)
    select  1 customer_id, 102 product_id, 1 quantity,
   TIMESTAMP '2024-04-03 00:00:00' + INTERVAL '23:27' HOUR TO MINUTE + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)  * -1 +   ((LEVEL-1) * interval '0.007125' second) 
           as purchase_date
    from    dual
    connect by level <= 3 UNION all
select  1, 101, 1,
   TIMESTAMP '2024-05-10 00:00:57' + INTERVAL '07:17' HOUR TO MINUTE + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)  * -1 +   ((LEVEL-1) * interval '0.000120' second) 
    from    dual
    connect by level <= 2 UNION all
select  1, 101, 1,
   TIMESTAMP '2024-06-13 00:00:59.999999' + INTERVAL '23:14' HOUR TO MINUTE + ((LEVEL-1) * INTERVAL '1 00:00:00' DAY TO SECOND)  * -1 +   ((LEVEL-1) * interval '0.999999' second) 
    from    dual
    connect by level <= 1 UNION all
select  3, 103, 3,
   TIMESTAMP '2024-06-09 00:00:00' + INTERVAL '17:37' HOUR TO MINUTE + ((LEVEL-1) * INTERVAL '1 00:00:00' DAY TO SECOND)  * -1 +   ((LEVEL-1) * interval '0.009120' second) 
    from    dual
    connect by level <= 6;

CUSTOMER_ID FIRST_NAME LAST_NAME DAY_PERIOD SWING_PERIOD GRAVE_PERIOD NUM_PURCHASES 
1 FAITH AARON 2 1 3 6
2 Lisa Jones 0 0 0 0
3 Roz Doyle 0 6 0 6

You can subtract 7h15m from the times and then find which 8-hour period the time is in from the start of the (shifted) day and then pivot the result and join to the customers table:

SELECT c.customer_id,
       c.first_name,
       c.last_name,
       COALESCE(p.day_period, 0) AS day_period,
       COALESCE(p.swing_period, 0) AS swing_period,
       COALESCE(p.grave_period, 0) AS grave_period,
       COALESCE(p.day_period, 0)
       + COALESCE(p.swing_period, 0)
       + COALESCE(p.grave_period, 0) AS num_purchases
       
FROM   (
         SELECT customer_id,
                FLOOR(
                  ( TRUNC(purchase_date - INTERVAL '07:15' HOUR TO MINUTE, 'HH')
                  - TRUNC(purchase_date - INTERVAL '07:15' HOUR TO MINUTE, 'DD')
                  ) * 3
                ) AS period
         FROM   purchases
       )
       PIVOT (
         COUNT(period)
         FOR period IN (0 AS day_period, 1 AS swing_period, 2 AS grave_period)
       ) p
       RIGHT OUTER JOIN customers c
       ON c.customer_id = p.customer_id

or, if you wanted to use EXTRACT and a CASE expression rather than arithmetic:

SELECT c.customer_id,
       c.first_name,
       c.last_name,
       COALESCE(p.day_period, 0) AS day_period,
       COALESCE(p.swing_period, 0) AS swing_period,
       COALESCE(p.grave_period, 0) AS grave_period,
       COALESCE(p.day_period, 0)
       + COALESCE(p.swing_period, 0)
       + COALESCE(p.grave_period, 0) AS num_purchases
       
FROM   (
         SELECT customer_id,
                CASE
                WHEN EXTRACT(HOUR FROM purchase_date - INTERVAL '07:15' HOUR TO MINUTE) < 8
                THEN 0
                WHEN EXTRACT(HOUR FROM purchase_date - INTERVAL '07:15' HOUR TO MINUTE) < 16
                THEN 1
                ELSE 2
                END AS period
         FROM   purchases
       )
       PIVOT (
         COUNT(period)
         FOR period IN (0 AS day_period, 1 AS swing_period, 2 AS grave_period)
       ) p
       RIGHT OUTER JOIN customers c
       ON c.customer_id = p.customer_id

Which, for the sample data, both output:

fiddle