SQL

WITH m AS(
  SELECT *, row_number() OVER(PARTITION BY year(OrderDate),month(OrderDate) 
     ORDER BY OrderDate) r 
  FROM Orders
  WHERE OrderDate>='2021-01-01')
SELECT * 
FROM m 
WHERE r=1

SPL

A3 Get the first record from each group. The first parameter in top(1,0) means the number of records to be retrieved; the second parameter means getting top n records ordered by this parameter. Here parameter 1 means getting one record. The second parameter, which is a constant here, means not sorting. The target is to get the first record from the grouped subset according to the original order after data is grouped.

SQL

WITH m AS(
  SELECT *,year(OrderDate) years,month(OrderDate) months, 
     row_number() OVER(PARTITION BY year(OrderDate),month(OrderDate) 
       ORDER BY OrderDate) r 
  FROM Orders
  WHERE OrderDate>='2021-01-01'),
  m1 AS(SELECT years,months,max(r) r FROM m GROUP BY years,months)
SELECT * 
FROM m,m1
WHERE m.r=m1.r and m.years=m1.years and m.months=m1.months

SPL

A3 Get the last record from each group. The first parameter being negative in top(-1,0) means getting n records backwards from each group ordered by the second parameter.

SQL

WITH m AS(
  SELECT *, row_number() OVER(PARTITION BY CustomerID ORDER BY OrderDate) r 
  FROM Orders
  WHERE OrderDate>='2021-01-01')
SELECT month(OrderDate) AS Months,count(*) Num
FROM m 
WHERE r=1
GROUP BY month(OrderDate)

SPL

We can also write A3 using group@1():

SQL

WITH m AS(
  SELECT *,row_number() OVER(PARTITION BY CustomerID ORDER BY 
OrderDate) r 
  FROM Orders
  WHERE OrderDate>='2022-01-01'),
m1 AS(SELECT CustomerID,max(r) r FROM m GROUP BY CustomerID)
SELECT month(m.OrderDate) AS Months,count(*) Num
FROM m,m1
WHERE m.r=m1.r and m.CustomerID=m1.CustomerID
GROUP BY month(m.OrderDate)

SPL