https://www.growthbook.io/ logo
m

miniature-jewelry-48222

05/02/2022, 6:45 PM
Hey Growthbook team! I need to include a
table( flatten( input => parse_json() ) )
sub-query as part of one of my metrics. The Metrics SQL editor is failing on the '=>' though. There's lots of additional context I can share, but wanted to keep this concise. Can I jump on a huddle with someone to troubleshoot? Thanks!
f

future-teacher-7046

05/02/2022, 6:46 PM
Hi John, what data source are you using? I'm not familiar with that SQL syntax
m

miniature-jewelry-48222

05/02/2022, 6:46 PM
Hi @future-teacher-7046! We use Snowflake
Snowflake query here: SELECT u.identity as user_id, o.user_id as anonymous_id, o.time as timestamp, quantity_lookup.value as value FROM ( SELECT quantity_lookup.order_id, sum(items.value['quantity']) as value FROM ( SELECT * FROM ( SELECT * ,dense_rank() over (partition by order_number order by session_id desc) as rank FROM _2_CRO_CONVERSION_SHOPIFY_WEB_POST_CONFIRMED_ORDER WHERE time > '2021-11-01' ORDER BY time asc ) WHERE rank = 1 ) as quantity_lookup, table( flatten( input => try_parse_json(quantity_lookup.line_items) ) ) items GROUP BY quantity_lookup.order_id ORDER BY quantity_lookup.order_id desc ) as quantity_lookup LEFT OUTER JOIN _2_CRO_CONVERSION_SHOPIFY_WEB_POST_CONFIRMED_ORDER as o on o.order_id = quantity_lookup.order_id LEFT OUTER JOIN users as u on u.user_id = o.user_id
f

future-teacher-7046

05/02/2022, 6:48 PM
Let me check something really quick. It's likely a bug with our SQL formatter
m

miniature-jewelry-48222

05/02/2022, 6:48 PM
Additional context: I'm trying to do an Average Basket Size analysis. We don't have an already existing column for basket size, so I'm using this query to create it on the fly
Awesome thank you
f

future-teacher-7046

05/02/2022, 6:51 PM
It's transforming the query to
input = > try_parse
(space after the equals sign) before running it, which is likely causing the problem
m

miniature-jewelry-48222

05/02/2022, 6:52 PM
I see. Is there anything I can do to work within the SQL formatter logic to fix that?
f

future-teacher-7046

05/02/2022, 6:55 PM
No, but I think can fix it pretty quickly in the build
m

miniature-jewelry-48222

05/02/2022, 6:56 PM
You rock, thank you!
Will you let me know when that's done and I'll try to re-run?
f

future-teacher-7046

05/02/2022, 6:56 PM
yep, should be about 20-30 minutes
m

miniature-jewelry-48222

05/02/2022, 6:56 PM
Awesome
f

future-teacher-7046

05/02/2022, 7:34 PM
Ok, the latest version is live now if you wanted to try re-running the query
If you're self-hosting, you'll need to pull the latest Docker container (app.growthbook.io updates automatically)
m

miniature-jewelry-48222

05/02/2022, 8:02 PM
My man! Thanks so much for jumping on this. I am all set
Just to close the loop for you, here you can see how this is being -- We're trying to understand how average basket sizes varies based on a price experiment we're running Basket size is not Revenue, but it is an order attribute -- You all don't have a metric concept to really capture this, but if I ignore the $ sign the revenue metric gets me there Food for thought on if you want to add a 'average of an event property' or something like that as a metric long term, but we're good for now!
f

future-teacher-7046

05/04/2022, 4:58 PM
The count metric type is basically the same as revenue, but without dollar signs when we display it.
2 Views