SQL Query new charts "time based"

Help me!
1

Hello,

I would like to count the number of user who order for the first time and display on a time based charts.

I use this sql query :

SELECT date_trunc(‘month’, “nth_order”) AS key, COUNT(DISTINCT “userId”) AS value
FROM (
SELECT “userId”, NTH_VALUE(“createdAt”, 1) OVER (PARTITION BY “userId” ORDER BY “createdAt” ASC) AS nth_order
FROM orders
GROUP BY “userId”, “createdAt”
) AS foo
GROUP BY date_trunc(‘month’, “nth_order”);

but i have an error :
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘key, COUNT(DISTINCT “userId”) AS value FROM ( SELECT “userId”, NTH_VALUE("create’ at line 1

What do you think of my sql query ? How could i fix it?

2

Hi @Benjamin

We are dedicated in this forum about the ForestAdmin tool.
Since this question pure SQL, I suggest you to ask it on a general or SQL forum.

One good forum for that is https://dba.stackexchange.com/

Regards

3

Hi,
I had the exact same problem, I just put ‘key’ in quotes and it worked

1 Like