Oracle, SQL request to retrieve data by week

Question

I have a database where there is logs of action performed by users, I want to identify the number of users by week which the ID changed From K beginning to A beginning, between the 01/01/2019 till today (20/06/2019) , in this example the user 1000 changed his ID from K to A because the last date action in K is older than the first action with A , the userID is unique of each user , here is my table, also the user 1002 changed at well for the same reason.

My table of logs looks like that

ID      date                  action         USERID
KF12    01/01/2019             Create        1000
KG45    11/06/2019             Create        1002
KI89    06/05/2019             Modify        1003
AO22    20/03/2019             Delete        1000
AI88    20/06/2019             Delete        1002

..

WHERE is what I tried, it's not fully complete, but I have no idea how to count changes by week

select distinct USERID, max(DATE_USER) over (partition by USERID) 
FROM
HISTORY 
WHERE 

USERID in (Select distinct USERID
from HISTORY
where ID like 'K%'
and DATE_USER >= to_date('1.1.' || 2019, 'DD.MM.YYYY') 
and DATE_USER < to_date('20.06.' || 2019 , 'DD.MM.YYYY')
INTERSECT
select distinct USERID
from HISTORY
where ID like 'A%'
and DATE_USER >= to_date('1.1.' || 2019, 'DD.MM.YYYY') 
and DATE_USER < to_date('19.06.' || 2019 , 'DD.MM.YYYY'))
and ID like 'A%'
;

In this example the expected result is the users (1000 , 1002) who changed at (20/03/2019,20/06/2019), the result have to be like this

WEEKNUMBER    COUNTOFCHANGE
25            1
12            1

Answer 1

Instead of using functions, You can try to use self join to achieve the same as the following:

-- DATA PREPARATION
WITH LOGS(ID, "DATE",ACTION, USERID) AS
(SELECT 'KF12',TO_DATE('01/01/2019','DD/MM/RRRR'),'Create',1000 FROM DUAL UNION ALL
SELECT 'KG45',TO_DATE('11/06/2019','DD/MM/RRRR'),'Create',1002 FROM DUAL UNION ALL
SELECT 'KI89',TO_DATE('06/05/2019','DD/MM/RRRR'),'Modify',1003 FROM DUAL UNION ALL
SELECT 'AO22',TO_DATE('20/03/2019','DD/MM/RRRR'),'Delete',1000 FROM DUAL UNION ALL
SELECT 'AI88',TO_DATE('20/06/2019','DD/MM/RRRR'),'Delete',1002 FROM DUAL)
-- ACTUAL QUERY
SELECT
    WK,
    COUNT(DISTINCT USERID)
FROM
    (
        SELECT
            TO_CHAR(L2."DATE", 'WW') WK,
            L2.USERID
        FROM
            LOGS L1
            JOIN LOGS L2 ON ( L1.USERID = L2.USERID
                              AND L1."DATE" < L2."DATE"
                              AND L1.ID LIKE 'K%'
                              AND L2.ID LIKE 'A%' )
    )
GROUP BY
    WK

Output:

DB Fiddle demo

Cheers!!