SQL

Suppose you have two tables, "orders" and "customers", where "orders" contains information about orders and "customers" contains information about customers. Write an SQL query that calculates the average order value for each customer who has made at least 3 orders in the last month, and only includes customers who are located in the United States. The query should require a join between the two tables.

Data Scientist

Snap

Netflix

VeriSign

Criteo

ASML

Klarna

Did you come across this question in an interview?

Answers

Expert Answer

Anonymous

4.2Exceptional
SELECT

c.customer_id,

SUM(amount)  / COUNT(o.order_id)   AS average_order_value

FROM
orders o

JOIN  customers  c ON o.cust_id = c.customer_id

WHERE
location = 'USA' AND and o.order_date >= DATEADD( month, -1, GETDATE())

GROUP BY

c.customer_id

HAVING

COUNT(o.order_id) >= 3

  • Suppose you have two tables, "orders" and "customers", where "orders" contains information about orders and "customers" contains information about customers. Write an SQL query that calculates the average order value for each customer who has made at least 3 orders in the last month, and only includes customers who are located in the United States. The query should require a join between the two tables.
  • Find the average value of orders placed by customers located in the United States who have made at least 3 orders in the last month, by performing a join between the "orders" and "customers" tables.
  • Write an SQL query to determine the average order value for customers based in the United States who have made 3 or more orders in the last month, using data from the "orders" and "customers" tables.
  • Using a join between the "orders" and "customers" tables, develop an SQL query to calculate the average order value for customers based in the United States who have made at least 3 orders within the past month.
  • Determine the average order value for customers located in the United States who have made 3 or more orders within the last month, using data from the "orders" and "customers" tables. Use a join operation in your query.
  • Write an SQL query to find the average value of orders placed by customers located in the United States who have made at least 3 orders over the past month, by joining the "orders" and "customers" tables.
  • Develop an SQL query that uses a join between the "orders" and "customers" tables to calculate the average order value for United States-based customers who have made 3 or more orders in the last month.
  • Calculate the average value of orders placed by customers located in the United States who have made at least 3 orders in the last month, using data from the "orders" and "customers" tables. Use a join operation in your query.
  • Write an SQL query to determine the average order value for customers based in the United States who have made 3 or more orders in the last month, using a join between the "orders" and "customers" tables.
  • Find the average value of orders placed by customers located in the United States who have made at least 3 orders over the past month, by performing a join between the "orders" and "customers" tables in your SQL query.
  • Calculate the average order value for customers located in the United States who have made at least 3 orders in the past month, using data from the "orders" and "customers" tables and a join operation.
Try Our AI Interviewer

Prepare for success with realistic, role-specific interview simulations.

Try AI Interview Now

Interview question asked to Data Scientists interviewing at Criteo, Affirm, Snap and others: Suppose you have two tables, "orders" and "customers", where "orders" contains information about orders and "customers" contains information about customers. Write an SQL query that calculates the average order value for each customer who has made at least 3 orders in the last month, and only includes customers who are located in the United States. The query should require a join between the two tables..