thiagoooo.

[azure swift]

What does your query look like right now?

[sturdy anchor]

I'm trying to create a sql that brings this data to me in sigma but the table is coming up blank, it may be that something is wrong

[azure swift]

Could be. What does the query look like right now?

[sturdy anchor]

SELECT customers.name, customers.id
FROM customers
INNER JOIN subscriptions ON customers.id = subscriptions.customer_id
WHERE subscriptions.status = 'delete'
AND NOT EXISTS (
SELECT *
FROM subscriptions
WHERE subscriptions.customer_id = customers.id
AND subscriptions.status = 'new'
)

[azure swift]

what time period do you want to see the customers who canceled?

[sturdy anchor]

DD/MM/YYYY

01/01/2023 - 31/07/2023

[azure swift]

You just need to use subscriptions.cancel_at if you want to see when the subscription moved into a canceled status, or you can use subscriptions.canceled_at if you want to see when the customer chose to cancel their subscription

Does that get you oriented in the right direction?

[sturdy anchor]

yes, it already guides me, can you fix this syntax for me?

[azure swift]

Do you know how to filter on a timestamp? What have you tried so far?

[sturdy anchor]

yes, i know

[azure swift]

So you have already filtered on the timestamp? Can I see your query with that line in it?

[sturdy anchor]

sorry, I haven't done it yet, I'm going to do it now

[azure swift]

I would suggest starting with this (and changing the dates), then add one conditional statement at a time to see why no records are showing up

SELECT customers.name, customers.id FROM customers INNER JOIN subscriptions ON customers.id = subscriptions.customer_id WHERE subscriptions.cancel_at >= cast('2023-01-01' as timestamp) AND subscriptions.cancel_at <= cast('2023-01-31' as timestamp)

[sturdy anchor]

i will try

no results could be found, I tried to fix it using gpt chat and it didn't work either. I will try more manually using the exports already ready inside the stripe. Thanks for the help and anything I ask for help here again.

[azure swift]

What if you just run this:
SELECT customers.name, customers.id FROM customers INNER JOIN subscriptions ON customers.id = subscriptions.customer_id

[sturdy anchor]

but, this syntax return customers that subscriptions actives or canceled?