SQL query to return records filtered by week, month , quarter, year-CodePudding

I have a table with the user and DateTime ( format: 12-OCT-22 01.15.23.000 AM). I need to get the user's count based on week, month, quarter, and year.

Like how many users per week, month, quarter, and year. In addition, to that eliminate duplicate records ( for example if a user's id registers twice in a week on the table records, we need to consider only one user) as well.

Need to write the SQL query for this.

ID1     12-OCT-22 01:40:56.880 AM
ID8     12-OCT-22 01:40:56.880 AM
ID6     12-OCT-22 01:40:56.880 AM
ID7     12-OCT-22 01:39:56.880 AM
ID4     12-OCT-22 01:38:56.880 AM
ID10    11-OCT-22 01:37:56.880 PM
ID43    11-OCT-22 01:36:56.880 PM
ID72    11-OCT-22 01:35:56.880 PM
.
.
.
Etc.

Note: I'm using PLSQL application and Oracle DB.

CodePudding user response：

To group by year try the code below, where T is the table that holds your data:

SELECT 
  TO_CHAR(DateTime, 'YYYY'), COUNT(*)
FROM T
GROUP BY
  TO_CHAR(DateTime, 'YYYY')

To group by week, replace YYYY with WW.
To group by month, replace YYYY with MM.
To group by quarter, replace YYYY with Q.
To count the distinct number of users then replace COUNT(*) with COUNT(DISTINCT USER_ID)

If my answer covers you, then please accept it.

CodePudding user response：

Use conditional aggregation:

SELECT userid,
       COUNT(1) AS count_for_previous_12_months,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE, 'YYYY') THEN 1 END) AS count_for_current_year,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE, 'Q') THEN 1 END) AS count_for_current_quarter,
       COUNT(CASE WHEN datetime >= ADD_MONTHS(TRUNC(SYSDATE), -1) THEN 1 END) AS count_for_previous_month,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE, 'MM') THEN 1 END) AS count_for_current_month,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE) - INTERVAL '7' DAY THEN 1 END) AS count_for_previous_seven_days,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE, 'IW') THEN 1 END) AS count_for_current_iso_week
FROM   table_name
WHERE  datetime > ADD_MONTHS(SYSDATE, -12)
AND    datetime <= SYSDATE
GROUP BY userid

If you want to remove duplicates in each week then aggregate by user and week first and then count afterwards:

SELECT userid,
       COUNT(1) AS count_for_previous_12_months,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE, 'YYYY') THEN 1 END) AS count_for_current_year,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE, 'Q') THEN 1 END) AS count_for_current_quarter,
       COUNT(CASE WHEN datetime >= ADD_MONTHS(TRUNC(SYSDATE), -1) THEN 1 END) AS count_for_previous_month,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE, 'MM') THEN 1 END) AS count_for_current_month,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE) - INTERVAL '7' DAY THEN 1 END) AS count_for_previous_seven_days,
       COUNT(CASE WHEN datetime >= TRUNC(SYSDATE, 'IW') THEN 1 END) AS count_for_current_iso_week
FROM   (
  SELECT userid,
         -- Remove duplicates for each week and set the datetime as the latest in each week
         MAX(datetime) AS datetime
  FROM   table_name
  GROUP BY userid, TRUNC(datetime, 'IW')
)
WHERE  datetime > ADD_MONTHS(SYSDATE, -12)
AND    datetime <= SYSDATE
GROUP BY userid

fiddle