Skip to content

Instantly share code, notes, and snippets.

@vandorjw
Created November 11, 2019 16:28
Show Gist options
  • Save vandorjw/1579a8b0891267ea3d55ee3e6e94815e to your computer and use it in GitHub Desktop.
Save vandorjw/1579a8b0891267ea3d55ee3e6e94815e to your computer and use it in GitHub Desktop.
-- DOUBLE ACTIVE SUBSCRIPTIONS
select
s.subscription_id,
o.order_id,
s.next_billing_date as sub_billing_date,
o.billing_date as order_billing_date,
o.status as order_status,
s.status as subscription_status,
o.first_name,
o.last_name
from
subscriptions s
right join orders o on
s.subscription_id = o.subscription_id
where
s.subscription_id in (
select
double_active.subscription_id
from
(
select
s.subscription_id,
count(*) as amount
from
orders o
left join subscriptions s on
o.subscription_id = s.subscription_id
where
o.status = 'Active'
and s.subscription_id is not NULL
group by
s.subscription_id
having
amount > 1) as double_active)
order by s.subscription_id;
@vandorjw
Copy link
Author

count all subscriptions without an active order

select
	count(*)
from
	subscriptions s
where
	s.subscription_id not in (
	select
		o.subscription_id
	from
		orders o
	where
		o.status = 'Active')
	and s.status in ('Active', 'Skipped');

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment