Oracle: Calculate the count() based on the past 6 month interval for each rows

Oracle doesn’t like COUNT( DISTINCT ... ) OVER ( ... ) when used in a windowed analytic function with a range and will raise an ORA-30487: ORDER BY not allowed here exception (otherwise, that would be the solution). It will work without the DISTINCT keyword but not with it.

Instead, you can use a correlated sub-query:

SELECT t.*,
       ( SELECT COUNT( DISTINCT vehicle_id )
         FROM   table_name c
         WHERE  c.emp_id = t.emp_id
         AND    c."DATE" <= t."DATE"
         AND    ADD_MONTHS( t."DATE", -6 ) <= c."DATE"
       ) AS last_6m_uniq_vehicle_count,
       COUNT(t.vehicle_id) OVER (
         PARTITION BY t.emp_id 
         ORDER     BY t."DATE"
         RANGE BETWEEN INTERVAL '6' MONTH PRECEDING
               AND     CURRENT ROW
      ) AS last_6m_vehicle_count
FROM  table_name t

Which for the sample data:

CREATE TABLE table_name ( vehicle_id, emp_id, "DATE" ) AS
SELECT 1, 1, DATE '2020-08-31' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-07-31' FROM DUAL UNION ALL
SELECT 1, 1, DATE '2020-06-30' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-05-31' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-04-30' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-03-31' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-02-29' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-01-31' FROM DUAL UNION ALL
SELECT 3, 1, DATE '2020-01-31' FROM DUAL;

Outputs:

VEHICLE_ID | EMP_ID | DATE      | LAST_6M_UNIQ_VEHICLE_COUNT | LAST_6M_VEHICLE_COUNT
---------: | -----: | :-------- | -------------------------: | --------------------:
         2 |      1 | 31-JAN-20 |                          2 |                     2
         3 |      1 | 31-JAN-20 |                          2 |                     2
         2 |      1 | 29-FEB-20 |                          2 |                     3
         2 |      1 | 31-MAR-20 |                          2 |                     4
         2 |      1 | 30-APR-20 |                          2 |                     5
         2 |      1 | 31-MAY-20 |                          2 |                     6
         1 |      1 | 30-JUN-20 |                          3 |                     7
         2 |      1 | 31-JUL-20 |                          3 |                     8
         1 |      1 | 31-AUG-20 |                          2 |                     7

db<>fiddle here