Skip to content

Latest commit

 

History

History
25 lines (21 loc) · 907 Bytes

ops.md

File metadata and controls

25 lines (21 loc) · 907 Bytes
Raw

Ops

Detect duplicate orders

This will return the orders coming from the same and to collective and same amount within the same minute

with res as (
  SELECT 
    substring(cast(DATE_TRUNC('minute', o."createdAt") as text) from 1 for 16) as minute,
    count(*) as count, "FromCollectiveId", "CollectiveId", "totalAmount", max(o."currency") as currency,
    o.description,
    MAX(s."isActive"::INT) as "activeSubscription"
  FROM "Orders" o
  LEFT JOIN "Subscriptions" s ON o."SubscriptionId"=s.id
  WHERE (s."isActive" IS TRUE OR s.id IS NULL) AND o."deletedAt" IS NULL AND o."processedAt" IS NOT NULL AND o."PaymentMethodId" IS NOT NULL
  GROUP BY "FromCollectiveId", "CollectiveId", "totalAmount", "minute", o."description"
  ORDER BY count DESC
)
SELECT fc.slug, fc.type, r.*
FROM res r
LEFT JOIN "Collectives" fc ON fc.id=r."FromCollectiveId"
WHERE count > 1 ORDER BY r.count DESC