'SQL Find unused table in the past 30 days

Let's say I have a table that has table_name, create_time, run_time, query. I would like to generate a list of tables that have not been used in the last 30 days and a ranking of the tables most used in the last 30 days.

I know how to extract a table within 30 days with BETWEEN but I do not know how to extract those who are not used in those 30 days.

sql


Solution 1:[1]

Minor changes might be needed depending on your DBMS, but this should very close

Tables Without Queries in Past 30 Days

SELECT table_name,MAX(run_time) AS LastQueryDate
FROM YourTable
GROUP BY table_name 
HAVING MAX(run_time) <= '2022-02-01' /*Hard coded date for simplicity. Could also parameterize as current date - 30 days*/

Most Used Tables over Past 30 Days

SELECT TOP(10) 
      ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS UsageRank
      ,table_name
      ,COUNT(*) As QueryCount
FROM YourTable
WHERE run_time BETWEEN '2022-02-01' AND '2022-03-01'
GROUP BY table_name
ORDER BY COUNT(*) DESC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stephan

Related Questions