Grouping dates in Big query

Question

I've been trying to write a query on big query to get the difference between two times (Created at and resolved at) and get the average of it grouped by day.

This is the query I wrote:

SELECT 
    avg(datetime_diff(datetime(stats.resolved_at), datetime(created_at), HOUR)) as Difference, 
    date(tickets.created_at)
FROM fresh_desk.tickets 
WHERE 
    type = "Revision Request" 
    OR type = "Proof request" 
    AND stats.first_responded_at is NULL 
GROUP BY date(tickets.created_at)

The results are showing by date itself but when I'm building charts over this data on a BI tool, I'm getting the results with date grouped by timestamp as well. How can I avoid this to have the date grouped by day and nothing else? It should show average by day.

Answer 1

Another way to write this query could be

with T as (
  SELECT 
    datetime_diff(datetime(resolved_at), datetime(created_at), HOUR) as Difference, 
    date(created_at) as custom_date
FROM fresh_desk.tickets
WHERE 
    type = "Revision Request" 
    OR type = "Proof request" 
    AND first_responded_at is NULL 
GROUP BY date(created_at)
)

Select avg(Difference), custom_date from T group by custom_date

After getting the results you can use Datastudio to get a preview of your data and you can customize the chart to plot the custom_date (axis X)and the difference (axis Y)

If your current tool is using another data type for axis X, you should verify the type used by your tool when importing the data