jacobsimon - subscription query | Stripe Developers | Page 1

kindred isle Jun 10, 2022, 6:47 PM

#

Good question. I will see if we have any examples or something similar. Are you running in to an issue building this query now or is this just a first stop in case it is already done somewhere?

thorny totem Jun 10, 2022, 6:53 PM

#

Yeah I've tried to do it a few ways and not quite sure how to do it and generate a table per month. I can reason about how to query one month at a time but I'm not that much of a SQL ninja.

kindred isle Jun 10, 2022, 6:59 PM

#

Gotcha. I am not immediately finding example queries like this but am still looking. If you send me one of those queries I can try to help with just my SQL knowledge. Also checking in to if our support team can help you build this if I ultimately can't

thorny totem Jun 10, 2022, 7:03 PM

#

so here's an example. I feel like this is kind of right but I don't know how to say "show this for every month" and I'm also not sure if "ended_at" is the right field or I should be looking at "canceled_at"

select p.interval, count(*) from subscriptions s
join plans p on s.plan_id = p.id
where s.created between date '2020-05-01' and date '2020-05-31'
and s.ended_at > date '2020-05-31'
group by p.interval;

Screen_Shot_2022-06-10_at_12.02.20_PM.png

#

actually sorry it should have been any date < the start date, not between

kindred isle Jun 10, 2022, 7:21 PM

#

Gotcha. So it turns out our support can help direct you on how to find certain fields but the actual SQL query building can often be out of scope. I am bouncing between threads on here a bit but am happy to help try to build this query a bit. So to be clear, for each month you want to see how many subscriptions of each plan type were created?

thorny totem Jun 10, 2022, 7:21 PM

#

thanks so much @kindred isle! yeah I'd like to see the number of active subscriptions within that month - so new ones + ones that were still active from previous months

#

basically "at the end of each month, how many active monthly and annual subscribers were there?"

#

with the goal that the total lines up with the chart that stripe dashboard shows (I can play around with the fields to figure that out hopefully)

kindred isle Jun 10, 2022, 7:34 PM

#

Thank you for the clarification. I am still trying to figure some of this out and unfortunately I don't have test access to Sigma. One think that I am finding that can get you part of the way there is that you may also be able to group by month(created) as well, though that would only give you the number of subscriptions created that month (and wouldn't exclude subscriptions that were created but never became active)

thorny totem Jun 10, 2022, 7:37 PM

#

yeah that doesn't quite work in this case because we want it to be like a report by month, which isn't a property of the subscription itself. I know there must be a way to do this but I could also just run a bunch of individual sub queries and then select from them

kindred isle Jun 10, 2022, 7:59 PM

#

Unfortunately I am still not finding much on this. Happy to play around with it some more if you have more time. Honestly for the purely SQL parts of this, it may be good to go to Stack Overflow or a similar site. Also I don't think support can help with the SQL but they may be able to help you find another way to pull this data with a report

#jacobsimon - subscription query