SQL Server Rank() by date, Group and Join two tables including pagination -

having 2 tables in sql server:

shopcart:

id    userid    prodid    qt    date 1     21        p1        1     2017-02-18 2     21        p8        1     2017-02-19 3     21        p2        1     2017-02-20 4     22        p5        1     2017-02-21 5     22        p3        1     2017-02-22 6     23        p4        1     2017-02-23 

users:

id    user_name 21    john 22    james 23    alice 24    lily 

i want table shown articles newest date, grouped user_id,having 1 column (art_c) count of articles each user added shopcart (not qt, quantity), , joined table of users. also, want add pagination each row of result.

result want this:

id    userid    user_name    prodid   art_c   date 3     21        john         p2       3       2017-02-20 5     22        james        p3       2       2017-02-22 6     23        alice        p4       1       2017-02-23 

what best/fastest aproach so? i'm using request, , i'm not sure if good/fast way go:

with tb_a  (   select u_id,u_name,dat,art_c,row_number() on (order u_name) rgw   shopcart   left join    (select         a.userid u_id,        b.user_name u_name,        format(shop_date, 'dd.mm.yyyy hh:mm') dat,        rank() on (partition a.userid order shop_date desc) dest_rank,        count(a.id) on (partition a.userid order shop_date) art_c                     shopcart        left join              users b on a.userid=b.id   group         a.userid,b.name,shop_date,a.id) b on a.userid=b.u_id    dest_rank = 1   group u_id,u_name,dat,art_c )  select * tb_a rgw between 0 , 100 order rgw 

i think need use row_number() on shopcart. before join:

select u.*, sc.* (select sc.*,              row_number() on (partition sc.userid order date desc) seqnum       shopcart sc      ) sc join      users u      on sc.userid = u.id seqnum = 1; 

for pagination, add order by , can use offset , fetch next.

edit:

you can count well, using window functions:

select u.*, sc.* (select sc.*,              row_number() on (partition sc.userid order date desc) seqnum,              count(*) on (partition sc.userid) cnt       shopcart sc      ) sc join      users u      on sc.userid = u.id seqnum = 1;