There is a table storing the purchase line: Sales: Id, ProductId, CustomerId, DateCreated. We want to understand through what products customers "get" to our store. How could look like a request that displays the product and the number of cases when it was the first customer purchase.

    1 answer 1

    Specify the DBMS.

    In general, something like this:

    SELECT ProductId, COUNT(*) as Cnt FROM( SELECT ProductId, CustomerId, DateCreated, MIN(DateCreated)OVER(PARTITION BY CustomerId)MinDate FROM Sales )as T WHERE DateCreated = MinDate GROUP BY ProductId ORDER BY COUNT(*) DESC

    If there are no window functions, then:

     SELECT S.ProductId, COUNT(*) as Cnt FROM( SELECT CustomerId, MIN(DateCreated)MinDate FROM Sales GROUP BY CustomerId )as T JOIN Sales as S ON S.CustomerId = T.CustomerId AND S.MinDate=S.DateCreated GROUP BY S.ProductId ORDER BY COUNT(*) DESC
    • It is necessary to display the number of times. - newman
    • @newman, added - pegoopik