0

I'm following this RFM SQL tutorial and running the queries in python with the pymysql package.

I keep getting the error:

You have an error in your SQL syntax near '(order by last_order_date) as rfm_recency,

I have edited, moved around, copied, pasted the query into a 100 different configurations and can't figure out how to fix it.

My query:

query2 = '''

select customer_id, rfm_recency, rfm_frequency, rfm_monetary

from 
    (
        select customer_id,
            ntile(4) over (order by last_order_date) as rfm_recency,
            ntile(4) over (order by count_order) as rfm_frequency,
            ntile(4) over (order by sum_amount) as rfm_monetary


    from 

        (
           select customer_id,
               max(local_date) as last_order_date,
               count(*) as count_order,
               sum(amount) as sum_amount
           from transaction
           group by customer_id) as T

        ) as P

And the general python code:

con = pymysql.connect(
        host='127.0.0.1', port=server.local_bind_port, db='whatever', user='me',
        password='pass')
    
    
cur = con.cursor()
    
try:
    cur.execute(query2)

 except pymysql.Error as e:
        
        print(" Error is: %d: %s" %(e.args[0], e.args[1]))

The error is at the line ntile(4) over (order by last_order_date) as rfm_recency

Can anybody here figure out where I'm going wrong?

Barmar
  • 741,623
  • 53
  • 500
  • 612
SCool
  • 3,104
  • 4
  • 21
  • 49
  • What version of MySQL are you using? MySQL doesn't have window functions until 8.x.' – Barmar Jun 26 '20 at 14:59
  • Did you read the section "Postgres vs. MySQL" at the site you linked to? – Barmar Jun 26 '20 at 15:00
  • mysql server version: 5.7.15.... I guess this is the reason for the error. I am a SQL noob though.. can you recommend an alternative to the window functions? – SCool Jun 26 '20 at 15:03
  • https://stackoverflow.com/questions/31209608/quartiles-in-sql-query – Barmar Jun 26 '20 at 15:17

0 Answers0