SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) 
 Jun 12 2020 20:39:00 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE purchases (
  Serviceid INTEGER,
  Date DATE,
  User_id INTEGER
);

INSERT INTO purchases
  (Serviceid, Date, User_id)
VALUES
  ('20', '2-Jan-18', '40709217'),
  ('20', '2-Jan-18', '40709217'),
  ('40', '2-Jan-18', '40709217'),
  ('40', '2-Jan-20', '40709217'),
  ('50', '2-Jan-21', '40709217'),
  ('984', '22-Mar-18', '18246539'),
  ('269', '22-Mar-18', '18246539'),
  ('666', '1-Apr-18', '18246539');
8 rows affected

select * from purchases


Serviceid
Date
User_id


20
2018-01-02
40709217

20
2018-01-02
40709217

40
2018-01-02
40709217

40
2020-01-02
40709217

50
2021-01-02
40709217

984
2018-03-22
18246539

269
2018-03-22
18246539

666
2018-04-01
18246539


   SELECT
        *,
       DENSE_RANK() OVER (
           PARTITION BY User_id
           ORDER BY [Date] 
       ) as earliest_order,
       ROW_NUMBER() OVER (
           PARTITION BY User_id
           ORDER BY [Date] DESC, Serviceid
       ) as latest_order,
       ROW_NUMBER() OVER (
              PARTITION BY User_id,[Date]
              ORDER BY Serviceid DESC
       ) rank_priority
   FROM
           purchases
    ORDER BY User_id, [Date], Serviceid DESC


Serviceid
Date
User_id
earliest_order
latest_order
rank_priority


984
2018-03-22
18246539
1
3
1

269
2018-03-22
18246539
1
2
2

666
2018-04-01
18246539
2
1
1

40
2018-01-02
40709217
1
5
1

20
2018-01-02
40709217
1
3
2

20
2018-01-02
40709217
1
4
3

40
2020-01-02
40709217
2
2
1

50
2021-01-02
40709217
3
1
1


SELECT
    User_id,
    MAX(CASE WHEN earliest_order=1 and rp=1 THEN Serviceid END) as FirstServiceid,
    MAX(CASE WHEN earliest_order=2 and rp=1 THEN Serviceid END) as SecondServiceid,
    MAX(CASE WHEN earliest_order=1 and rp=1 THEN [Date] END) as FirstServiceDate,
    MAX(CASE WHEN earliest_order=2 and rp=1 THEN [Date] END) as SecondServiceDate,
    MAX(CASE WHEN latest_order=1 THEN Serviceid END) as LastServiceid,
    MAX(CASE WHEN latest_order=1 THEN [Date] END) as LastServiceDate,
    COUNT(1) as TotalService
FROM (
   SELECT 
       *,
       DENSE_RANK() OVER (
           PARTITION BY User_id
           ORDER BY [Date] 
       ) as earliest_order,
       ROW_NUMBER() OVER (
           PARTITION BY User_id
           ORDER BY [Date] DESC, Serviceid
       ) as latest_order,
       ROW_NUMBER() OVER (
              PARTITION BY User_id,[Date]
              ORDER BY Serviceid DESC
       ) rp
   FROM 
       purchases
   
) t
GROUP BY
    User_id
ORDER BY
    User_id DESC


User_id
FirstServiceid
SecondServiceid
FirstServiceDate
SecondServiceDate
LastServiceid
LastServiceDate
TotalService


40709217
40
40
2018-01-02
2020-01-02
50
2021-01-02
5

18246539
984
666
2018-03-22
2018-04-01
666
2018-04-01
3


Warning: Null value is eliminated by an aggregate or other SET operation.
Serviceid	Date	User_id
20	2018-01-02	40709217
20	2018-01-02	40709217
40	2018-01-02	40709217
40	2020-01-02	40709217
50	2021-01-02	40709217
984	2018-03-22	18246539
269	2018-03-22	18246539
666	2018-04-01	18246539