Request average call duration for all users per day

Question

A person uses his cell phone several times a day, and the duration of their calls varies. I track the length of calls in a table:

Calls [callID, memberID, startTime, duration]

I need a request to return the average length of a request for users per day . A day means that if the user used the phone 3 times, the first time within 5 minutes, the second within 10 minutes and the last time within 7 minutes, the calculation:5 + 10 + 7 / 3 = ...

Note:

• People don’t use the phone every day, so we should get the average daily day per person and use it to get the total average call duration.

• we don’t want to recount anyone on average, so only 1 line per user will calculate the average duration of a daily call.

Some clarifications ...

I need a total average value per day, based on the average for each user per day, using the numbers of the last days for users (since we only count the specified ONCE user in the request), so this will mean that we will be using different days of Wednesdays. as people may not use the phone every day or even on the same day.

Answer 1

You need to convert DATETIME to what you can do in groups "per day", so this will result in "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)

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

" ", .

Answer 2

.

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

1 . , " ". , SELECT ,

Answer 3

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