Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
Finding Duplicate Orders
00:00
5 left

Finding Duplicate Orders

MediumSQL · PostgreSQL

Problem

Amazon's order-quality team needs to identify orders that may have been created more than once. Treat orders as duplicates when they share the same customer_id, order_date, total_amount, and shipping_address.

Write a PostgreSQL query that returns each duplicate order group and the IDs of all orders in that group.

Requirements

  1. Use a CTE to identify business-key combinations occurring more than once.
  2. Treat matching NULL shipping addresses as equal for duplicate detection.
  3. Return the customer email, duplicate business-key values, duplicate count, and a comma-separated list of order IDs.
  4. Sort by duplicate count descending, then customer ID ascending.

Output details

  • Return columns in this order: customer_id, customer_email, order_date, total_amount, shipping_address, duplicate_count, duplicate_order_ids.
  • Sort ties after customer_id ascending by order_date ascending, then total_amount ascending with NULL values last.
  • List duplicate_order_ids in ascending order_id order, separated by ', '.
  • Treat matching NULL total_amount values as equal when identifying and retrieving duplicate groups.
  • Return NULL for customer_email when no matching customer exists.

Schema

orders
ColumnTypeDescription
order_idPKINTUnique order identifier
customer_idINTReferences customers.customer_id
order_dateDATEDate the order was placed
total_amountNUMERIC(10,2)Order total
shipping_addressVARCHAR(200)Delivery address
customers
ColumnTypeDescription
customer_idPKINTUnique customer identifier
emailVARCHAR(255)Customer email address
Tablesorderscustomers
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the two tables are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.