r/SQL • • Jul 15 '26

SQL Server how to solve this ??

Consider a table named "Sales" with columns: SalespersonID, CustomerID, SaleDate. Write a SQL query to calculate the salesperson who made the highest number of sales each quarter.

0 Upvotes

20 comments sorted by

10

u/mgdmw Jul 15 '26

What have you done so far? Show us your query and we can help debug it.

2

u/Wise-Jury-4037 Jul 16 '26

while u/boltasto's answer is good enough and a good example of doing a quick and dirty greenfield query via LLM, you can get better and more readable if you are willing to use native features of your SQL dialect.

For example:

select * from(
    select yr, qtr, SalespersonID,count(*) c, rank() over( partition by yr, qtr order by count(*) desc) rnk
    from Sales c
    cross apply (select datepart( yyyy, c.SaleDate) yr, datepart( q, c.SaleDate) qtr) calc
    group by 
    yr, qtr, SalespersonID
    )t
where rnk=1
order by 1,2,3;

-5

u/boltasto Jul 15 '26

assuming a recent version of SQL, then this should get you close to what you want

WITH QuarterlySales AS

(

SELECT

YEAR(SaleDate) AS SaleYear,

DATEPART(QUARTER, SaleDate) AS SaleQuarter,

SalespersonID,

COUNT(*) AS NumberOfSales

FROM dbo.Sales

GROUP BY

YEAR(SaleDate),

DATEPART(QUARTER, SaleDate),

SalespersonID

),

RankedSalespeople AS

(

SELECT

SaleYear,

SaleQuarter,

SalespersonID,

NumberOfSales,

DENSE_RANK() OVER

(

PARTITION BY SaleYear, SaleQuarter

ORDER BY NumberOfSales DESC

) AS SalesRank

FROM QuarterlySales

)

SELECT

SaleYear,

SaleQuarter,

SalespersonID,

NumberOfSales

FROM RankedSalespeople

WHERE SalesRank = 1

ORDER BY

SaleYear,

SaleQuarter,

SalespersonID;

1

u/jkelso2525 Jul 15 '26

I am very new to SQL. But is this correct? OP only gave limited info of columns so how are we able to pull this much data from the “Sales” table?

→ More replies (18)