Using google sheets - "Query function" to find during what time of the day the instance_id percentage is lowest

91 Views Asked by At

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'")

desired outcome

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" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... "/" ... "%" ... "+" ... "-" ...
0

There are 0 best solutions below