Metadocumentation
open-collective
open-collective
  • Welcome
  • About
    • Introduction
    • Terminology
    • Company
    • Pricing
    • Community Guidelines
    • Values
    • Team
    • Investors
    • Contributing
  • Product
    • Features
    • Roadmap
    • Comparison
    • User Profile
    • Currencies
    • Log-in System
  • Collectives
    • FAQ
    • Creating a Collective
    • Quick Start Guide
    • Customize Collective
    • Change Core Contributors
    • Tiers & Goals
    • Add Fiscal Host
    • Change Fiscal Host
    • Transparent Budget
    • Expense Policy
    • Approving Expenses
    • Updates & Comms
    • Events
    • Funding Options
    • Data Export
    • Buttons & Banners
    • Integrations
    • Zero Collective Balance
    • Closing a Collective
  • Financial Contributors
    • FAQ
    • Payments
    • Website Badge
    • Organizations
      • FAQ
      • Bulk Transfers
      • Gift Cards
      • Sustainer Resources
    • Collectives
      • Collective to Collective donations
  • Expenses & Getting Paid
    • FAQ
    • Submitting Expenses
    • Expense Comments
    • Edit an Expense
    • Tax Information
  • Fiscal Hosts
    • FAQ
    • Becoming a Fiscal Host
    • Create a Fiscal Host
    • Fiscal Host Settings
    • Invoices
    • Payouts
    • Host Dashboard
    • Add Funds Manually
    • Refunds
    • Host Fees
    • Local Tax Support
    • Agreement Templates
    • Open Source Collective
  • Contributing
    • Design
      • Design Workflow
      • Design Contribution Guidelines
    • Development
      • Contribution Guide
      • Best Practice Guidelines
      • Bounties
      • API
        • Members
        • Users / Emails
        • Collectives
        • Events
      • README Integration
      • Architecture
      • Postgres Database
      • PayPal
      • Post-Donation Redirect
      • Custom Tweets
      • Manual Reporting
      • Github Permissions
      • Translations
      • Testing with Cypress
      • Collective's locations
    • Documentation
      • Style guide
      • Suggesting changes
    • Translation
  • Internal
    • Scope
    • Team Retreats
    • Brussels Summer Team Retreat
    • Host Admin Manual
    • Newsletter
    • Support
    • Issue Labels
    • DNS Troubleshooting
    • Developer Guidelines
    • Architecture
    • Testing
    • Queries
      • Gift Cards
      • Transactions
      • Ops
      • Collectives
      • Hosts
      • Analytics
      • Emails
    • Projects
      • Maintainerati Berlin 2019
      • Season of Docs 2019
    • The Open Collective Way
      • Values
      • Community Guidelines
      • Core Contributors Guidelines
      • Core Contributors: Communication
      • Core Contributors: Expenses
      • Core contributors: Leave
      • Core Contributors: Compensation
Powered by GitBook
On this page
  1. Internal
  2. Queries

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
PreviousTransactionsNextCollectives

Last updated 5 years ago