Query for the average call length for all users for a day

Question

(sql server 2005)

A person uses their cell phone multiple times per day, and the length of their calls vary.

I am tracking the length of the calls in a table:

Table: Calls [callID, memberID, startTime, duration]

I need to a query to return the average call length for users PER DAY.

Per day means, if a user used the phone 3 times, first time for 5 minutes, second for 10 minutes and the last time for 7 minutes, the calculation is: 5 + 10 + 7 / 3 = ...

Note:

1. People don't use the phone everyday, so we have to get the latest day's average per person and use this to get the overall average call duration.

2. we don't want to count anyone twice in the average, so only 1 row per user will go into calculating the average daily call duration.

Update Some clarifications:

I want a overall per day average, based on the per-user per-day average, using the users latest days numbers (since we are only counting a given user ONCE in the query), so it will mean we will be using different days avg. since people might not use the phone each day or on the same day even.

Answer 1

select average(duration) from calls group by date(startTime);

Answer 2

You need to convert the DATETIME to something you can make "per day" groups on, so this would produce "yy/mm/dd".

SELECT
  memberId,
  CONVERT(VARCHAR, startTime, 102) Day,
  AVG(Duration) AvgDuration
FROM
  Calls
WHERE
  CONVERT(VARCHAR, startTime, 102) = 
  (
    SELECT 
      CONVERT(VARCHAR, MAX(startTime), 102) 
    FROM 
      Calls i WHERE i.memberId = Calls.memberId
  )
GROUP BY
   memberId,
   CONVERT(VARCHAR, startTime, 102)

Use LEFT(CONVERT(VARCHAR, startTime, 120), 10) to produce "yyyy-mm-dd".

For these kind of queries it would be helpful to have a dedicated "day only" column to avoid the whole conversion business and as a side effect make the query more readable.

Answer 3

The following query will get you the desired end results.

SELECT AVG(rt.UserDuration) AS AveragePerDay
FROM
(
    SELECT
     c1.MemberId,
     AVG(c1.Duration) AS "UserDuration"
    FROM Calls c1
    WHERE CONVERT(VARCHAR, c1.StartTime, 102) =
     (SELECT CONVERT(VARCHAR, MAX(c2.StartTime), 102)
      FROM Calls c2
      WHERE c2.MemberId = c1.MemberId)
    GROUP By MemberId
) AS rt

THis accomplishes it by first creating a table with 1 record for each member and the average duration of their calls for the most recent day. Then it simply averages all of those values to get the end "average call duration. If you want to see a specific user, you can run just the innser SELECT section to get the member list