Calculating monthly averages in SQL: Why is my query averaging the entire dataset?

Data and Databases
1

Hey everyone, I’m stuck on a SQL problem. I’m trying to calculate the average time spent in client meetings for each month, but my query is returning the same average for every month instead of separate monthly results. I tried using a query with Common Table Expressions to isolate the start month and then compute the average meeting duration, but no matter how I group the data, I end up with the same overall average for each month. I’ve attached a revised code snippet below that uses different column names and functions to see if that fixes my issue. Any insights into why the GROUP BY isn’t working as expected would be really appreciated.

WITH client_start AS (
  SELECT
    customer_id,
    EXTRACT(MONTH FROM MIN(start_date)) AS first_month
  FROM client_data
  GROUP BY customer_id
),
meeting_durations AS (
  SELECT
    customer_id,
    client_start.first_month,
    AVG(TIMESTAMPDIFF(MINUTE, meeting_start, meeting_end)) AS avg_duration
  FROM client_start
  JOIN meeting_logs ON client_start.customer_id = meeting_logs.customer_id
  GROUP BY client_start.first_month
)
SELECT
  avg_duration,
  first_month
FROM meeting_durations
ORDER BY first_month DESC;

What am I doing wrong? How can I adjust my approach to get distinct average meeting times for each month?

2

hey, looks like ur grouping might be off. in the meeting_durations CTE, try adding customer_id to the GROUP BY clause. also, make sure ur joining correctly - the ON condition should probably include the month too. hope this helps! lemme know if u need more info

3

hey CreativeChef89! have u considered using a PARTITION BY clause instead? it might help get those monthly averages ur after. also, double-check ur data - any chance there’s only one month of meetings? curious to hear more about ur dataset. what other columns do u have to work with?

4

I’ve encountered similar issues before, and the problem likely stems from your JOIN operation. In your current query, you’re joining client_start with meeting_logs only on customer_id, which doesn’t restrict the meetings to the specific month you’re interested in. This causes all meetings for each customer to be included in every month’s calculation.

To fix this, modify your JOIN condition to include both customer_id and the month. You can use EXTRACT(MONTH FROM meeting_start) to match with first_month. Also, ensure you’re grouping by both customer_id and first_month in the meeting_durations CTE.

If you’re still having trouble after these adjustments, consider sharing a sample of your data structure and some example rows. This would help in providing a more tailored solution to your specific dataset and requirements.