0

I have a Transaction table with transactions data :

"created" - DateTime of transaction
"price" - transaction price
"id":    product identifier

Sample data

id created price
5 2022-05-08 20:20:00 1
5 2022-05-08 19:00:00 2
5 2022-05-08 7:40:00 3
5 2022-05-05 8:20:00 4
2 2022-05-09 10:40:00 5
2 2022-05-09 10:40:00 6
2 2022-05-07 15:40:00 7
2 2022-05-03 16:30:00 8

Goal: to calculate the 25% percentile for prices and the number of transactions with prices lower than the 25 price percentile(25% percent of all) per id.

Expected result:

id price 1st q n_transactions
5 2 1
2 6 1

I have tried:

SELECT
    
    id,
    
    MAX(CASE WHEN Quartile = 1 THEN price END) 1Quartile,
FROM (
    SELECT
        id,
        price,
        NTILE(4) OVER (PARTITION BY id ORDER BY price) AS Quartile
    FROM
        Transactions) Vals
    GROUP BY
    id
    ORDER BY
    id

which should return the 1 quartile for the price but it returns only an execution error with the message "check SQL syntax". MySQL version: 5.7.36

John Doe
  • 3
  • 4
  • *MySQL version: 5.7.36* - this version does not support window functions. Upgrade the version or emulate the function. – Akina May 09 '22 at 15:05
  • The problem is your version doesn't support window functions. This post might contain the logic to recreate, but upgrading would be best because window functions are more efficient. https://stackoverflow.com/questions/61295183/convert-rank-over-partition-in-mysql-5-7 – Josh May 09 '22 at 17:46

0 Answers0