SQL sum of all records and latest record of each user

Question

I have a table like this:

id   memberId     memberType    points      date
---- ------------ ------------- ----------- ------------
1    1001         type1         5.5         01/01/2015
2    1002         type2         4.2         01/02/2015
3    1002         type2         2.1         01/15/2015
4    1001         type2         1.5         01/15/2015
5    1002         type1         3.6         01/17/2015

I need to make an SQL view that will show the sum of points for each memberId and their latest memberType like below:

memberId    Type           totalPoints
----------- -------------- -----------
1001        type2          7.0
1002        type1          9.9

I tried the query below:

SELECT memberId, MAX(memberType) as Type, SUM(points) as totalPoints
FROM dbo.PointsEarning
GROUP BY memberId

But of course this only works if the latest type is the max type. Plus some of my membertypes are purely alpha. I would appreciate if anyone can provide the most efficient way of doing this since I will be using it for a table with almost 30M records.

Answer 1

You can use sub-query with OVER clauses in following:

SELECT memberId, 
       memberType AS Type, 
       points AS totalPoints
FROM (SELECT memberId, 
             memberType, 
             date,
             SUM(points) OVER(PARTITION BY memberId) points,                 
             ROW_NUMBER() OVER(PARTITION BY memberId ORDER BY date DESC) rn
      FROM dbo.PointsEarning
)x
WHERE rn = 1

You can test It at SQL FIDDLE

Answer 2

;WITH CTE as
(
  SELECT
    SUM(points) OVER (PARTITION BY memberId) totalPoints,
    ROW_NUMBER() over (PARTITION BY memberId ORDER BY [date] DESC) rn,
    id, memberId, memberType, points, [date]
  FROM yourtable
)
SELECT
  * 
FROM CTE
WHERE rn = 1