Skip to content

Instantly share code, notes, and snippets.

@anonrig

anonrig/subscription.sql

Last active January 23, 2020 14:34
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save anonrig/3d1363ffc7b591989f7c9c2a954d9606 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save anonrig/3d1363ffc7b591989f7c9c2a954d9606 to your computer and use it in GitHub Desktop.
Download ZIP
subscription
Raw
subscription.sql
SELECT
"SubscriberID",
"StandardSubscriptionDuration" as "SubscriptionDuration",
MIN("EventDate") as "FirstTransaction",
MAX("EventDate") as "LastTransaction",
"ApplicationId",
CASE
WHEN MAX("EventDate") BETWEEN LOCALTIMESTAMP - ("StandardSubscriptionDuration")::interval AND LOCALTIMESTAMP THEN true ELSE false
END AS "isActive"
FROM "Subscriptions"
INNER JOIN "Applications"
ON "Applications"."id" = "Subscriptions"."ApplicationId"
GROUP BY "SubscriberID", "StandardSubscriptionDuration", "ApplicationId"
ORDER BY "LastTransaction" DESC
OFFSET 0
LIMIT 50;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment