Suppose there’s a log of security related incidences for about 60 days along with instance_id. Now, there are cases when the incident is reported but the incident_id is not. This should be done only via SQL (Query Function) in Google sheets and not any other tool.
Your goal is to find out what time of the day does the incident_id percentage is lower?
I tried using this query to get the following result:
=QUERY(instances, "SELECT
CASE
WHEN TIME(C) >= TIME '06:00:00' AND TIME(C) <= TIME '12:00:00' THEN 'Morning'
WHEN TIME(C) > TIME '12:00:00' AND TIME(C) <= TIME '18:00:00' THEN 'Afternoon'
WHEN TIME(C) > TIME '18:00:00' AND TIME(C) <= TIME '23:59:59' THEN 'Evening'
ELSE 'Night'
END, COUNT(D), (COUNT(D)/COUNT(C))*100
WHERE TODATE(C) = DATE '2022-04-11'
GROUP BY
CASE
WHEN TIME(C) >= TIME '06:00:00' AND TIME(C) <= TIME '12:00:00' THEN 'Morning'
WHEN TIME(C) > TIME '12:00:00' AND TIME(C) <= TIME '18:00:00' THEN 'Afternoon'
WHEN TIME(C) > TIME '18:00:00' AND TIME(C) <= TIME '23:59:59' THEN 'Evening'
ELSE 'Night'
END
ORDER BY COUNT(D) ASC
LABEL
CASE
WHEN TIME(C) >= TIME '06:00:00' AND TIME(C) <= TIME '12:00:00' THEN 'Morning'
WHEN TIME(C) > TIME '12:00:00' AND TIME(C) <= TIME '18:00:00' THEN 'Afternoon'
WHEN TIME(C) > TIME '18:00:00' AND TIME(C) <= TIME '23:59:59' THEN 'Evening'
ELSE 'Night'
END 'time_of_day', COUNT(D) 'instances', (COUNT(D)/COUNT(C))*100 'percentage'")
But, I'm getting the following error:
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " <ID> "WHEN "" at line 3, column 5. Was expecting one of: <EOF> "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... "/" ... "%" ... "+" ... "-" ...
