I have table Buyer

I need to select all BuyId's who consecutively had higher QuantityOrder in each new record

• buyid=1 had first order with quantity=10, second order with quantity=20
• buyid=2 had first order with quantity=50, second order with quantity=60

So BuyId=1 and 2 would enter the results, while 3 and 4 would be filtered out because they had only one order or they did not have orders with consecutively higher quantities ordered

I tried with this, and I'm aware that this query gives me only buyid's who have more than one order, but I am missing the rule where I have to filter results out by quantity increased with each new order

select buyid, count(*) as ordered
from buyer
group by buyid
having count(*) >1

How would I write out that rule in a query, to select only BuyId's who had multiple orders, and in each new order they ordered higher quantities than in previous orders?

CodePudding user response：

Taking advantage of the fact that count() doesn't takes into account NULL values:

select buyid from (
select buyid, count(QuantityOrdered) as norders, count(mod) as nqttyincreasing
from (
    select d.*, 
        case when nvl(lag(QuantityOrdered) over(partition by buyid order by dateordered),0) < QuantityOrdered then 1 end as mod
    from data d
)
group by buyid
) where norders = nqttyincreasing ;

CodePudding user response：

With this query, it will check if all purchases as bigger in quantity as the last and will not show buyers id. that have only one purchase or sold equal or less than the last time4

CREATE TABLE buyers
    ([BuyId] int, [QuantityOrdered] int, [dateordered] date)
;
    
INSERT INTO buyers
    ([BuyId], [QuantityOrdered], [dateordered])
VALUES
    (1, 10, '2021-11-04'),
    (1, 20, '2022-01-22'),
  (1, 30, '2022-02-22'),
    (2, 50, '2022-02-20'),
    (2, 60, '2022-05-02'),
    (3, 60, '2022-05-02'),
    (4, 10, '2022-05-02'),
    (4, 6, '2022-06-02')
;

8 rows affected

WITH CTE as (SELECT
[BuyId], [QuantityOrdered], [dateordered],
  (SELECT COUNT(*) FROM buyers b1 WHERE b1.[BuyId] = buyers.[BuyId]) _count_
,CASE WHEn [QuantityOrdered] > 
   LAG([QuantityOrdered]) OVER(PARTITION BY [BuyId] ORDER BY [dateordered])
THEn 1 
   ELSe 
      CASE WHEN LAG([QuantityOrdered]) OVER(PARTITION BY [BuyId] ORDER BY [dateordered]) IS NULL 
           THEN 1 ELSE 0 END
  END _bigger_
FROM buyers)
SELECT DISTINCT [BuyId] FROM CTE 
  WHERE _count_ > 1 AND NOT EXISTS ( SELECT 1 FROM CTE c1 
                     WHERE c1.[BuyId] = CTE.[BuyId] AND c1._bigger_ = 0)

fiddle

CodePudding user response：

To select buyers who increased their order quantity with each consecutive order, you can use a combination of the GROUP BY and HAVING clauses in your query.

Here's an example of how you could modify your query to select only BuyId's who had multiple orders and increased their order quantity with each new order:

SELECT buyid
FROM buyer
GROUP BY buyid
HAVING COUNT(*) > 1
   AND SUM(CASE WHEN QuantityOrdered > LAG(QuantityOrdered) OVER (PARTITION BY buyid ORDER BY dateordered) THEN 1 ELSE 0 END) = COUNT(*) - 1

This query uses the GROUP BY clause to group the records by buyid, and the HAVING clause to filter the results to only include buyid values that have more than one record and have increased their order quantity with each new order.

The HAVING clause uses the LAG() window function to compare the QuantityOrdered of each record to the QuantityOrdered of the previous record for the same buyid, and uses the SUM() function to count the number of records where the QuantityOrdered is greater than the previous QuantityOrdered. It then compares this count to the total number of records for each buyid (minus 1, to account for the first record) to determine whether the buyid increased its order quantity with each new order.

I hope this helps! Let me know if you have any other questions.