I’m trying to create a table called “Stats” that employees use a business application where they perform searches and projects on it. I’m trying to answer the following questions with the SQL queries:
-
How many different employees used the business application within a certain time range?
-
How many searches were performed within a certain time range?
-
What was the average search time within this time range?
Here’s the table:
CREATE TABLE Stats (
employee_id INT PRIMARY KEY,
number_of_employees INT,
search VARCHAR(50),
number_of_results INT,
start_time timestamp with time zone,
completion_time timestamp with time zone
)
INSERT INTO Stats (employee_id, number_of_employees, search, number_of_results, start_time, completion_time)
VALUES (001, 125, “organization”, 2000, 2020-04-18 12:13:21.101+00, 2020-04-18 12:13:26.53+00)
INSERT INTO Stats (employee_id, number_of_employees, search, number_of_results, start_time, completion_time)
VALUES (005, 127, “organization”, 2000, 2020-04-18 12:14:32.877+00, 2020-04-18 12:16:53.43+00)
I tried to query the distinct number of employees by creating parameters to find the needed info within the time range, but I keep getting errors and I don’t know why? Here’s what I have:
SELECT DISTINCT COUNT(employee_id), COUNT(“search”), AVG(number_of_results), COUNT(*), run_at, completion_time
FROM Stats
WHERE employee_id =
(
SELECT employee_id, ROW_NUMBER() OVER(PARTITION BY “search”)
FROM Stats
);
CREATE PROCEDURE timerange(
StartDATE = date(start_time),
EndDATE = date(completion_time),
StartTIME = timestamp(start_time),
EndTIME = timestamp(completion_time)
DATEDifference DATE = EndDATE – StartDATE
TimeDifference TIME = ENDTIME – StartTIME
CertainRange = CONCAT(DateDifference || TimeDifference) AS CertainTimeRange
)
LANGUAGE plpgsql
AS $$
BEGIN
(SELECT CertainRange
FROM Stats
WHERE start_time >= StartDATE AND completion_time >= EndDATE AND completion_time >= EndDATE AND completion_time >= EndTIME
ORDER BY employee_id ASC LIMIT 2);
END;
$$;
I get an error when I use the time function, not sure why?