subscription

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;