# 8 Etsy SQL Interview Questions (Updated 2024)

Updated on

March 2, 2024

At Etsy, SQL is used day-to-day for analyzing customer shopping patterns, and managing inventory data to track popular products and forecast sales trends. Because of this, Etsy almost always asks SQL coding questions during interviews for Data Analyst, Data Science, and BI jobs.

As such, to help you prepare for the Etsy SQL interview, we've curated 8 Etsy SQL interview questions – able to answer them all?

## 8 Etsy SQL Interview Questions

### SQL Question 1: Same Week Purchases

You are given the two tables containing information on Etsy’s user signups and purchases. Write a query to obtain the percentage of users who signed up and made a purchase within 7 days of signing up. The result should be rounded to the nearest 2 decimal places.

#### Table:

Column NameType
user_idinteger
signup_datedatetime

#### Example Input:

user_idsignup_date
44506/21/2022 12:00:00
74206/19/2022 12:00:00
64806/24/2022 12:00:00
78906/27/2022 12:00:00
12306/27/2022 12:00:00

#### Table:

Column NameType
user_idinteger
product_idinteger
purchase_amountdecimal
purchase_datedatetime

#### Example Input:

user_idproduct_idpurchase_amountpurchase_date
244757545.0006/22/2022 12:00:00
742124150.0006/28/2022 12:00:00
648363255.5006/25/2022 12:00:00
123847567.3006/29/2022 12:00:00
244234174.1006/30/2022 12:00:00

### Example Output:

single_purchase_pct
40.00

This is the same question as problem #29 in the SQL Chapter of Ace the Data Science Interview!

The PostgreSQL query to solve this problem is:

To practice this question on DataLemur's free interactive SQL code editor, solve this Etsy SQL question asked in a BI Engineer interview:

### SQL Question 2: First Transaction

This is the same question as problem #9 in the SQL Chapter of Ace the Data Science Interview!

Assume you're given a table containing Etsy user transactions. Write a query that retrieves the customers whose first transaction was valued at \$50 or more. Output the total number of users who meet this criteria.

#### Table:

Column NameType
transaction_idinteger
user_idinteger
spenddecimal
transaction_datetimestamp

#### Example Input:

transaction_iduser_idspendtransaction_date
75927411149.5002/03/2022 00:00:00
85037111151.0003/15/2022 00:00:00
61534814536.3003/22/2022 00:00:00
137424156151.0004/04/2022 00:00:00
24847515687.0004/16/2022 00:00:00

#### Example Output:

users
1

Try this question on DataLemur's interactive coding environment, and get access to hints and more!

### SQL Question 3: What's a constraint in SQL, and do you have any examples?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:

Say you were storing sales analytyics data from Etsy's CRM inside a database. Here's some example constraints you could use:

• PRIMARY KEY constraint: You might use a PRIMARY KEY constraint to ensure that each record in the database has a unique identifier. For example, you could use the "opportunity_id" field as the primary key in the "opportunities" table.

• FOREIGN KEY constraint: You might use a FOREIGN KEY constraint to link the data in one table to the data in another table. For example, you could use a foreign key field in the "opportunities" table to reference the "account_id" field in the "accounts" table.

• NOT NULL constraint: You might use a NOT NULL constraint to ensure that a field cannot contain a NULL value. For example, you could use a NOT NULL constraint on the "opportunity_name" field in the "opportunities" table to ensure that each opportunity has a name.

• UNIQUE constraint: You might use a UNIQUE constraint to ensure that the data in a field is unique across the entire table. For example, you could use a UNIQUE constraint on the "email" field in the "contacts" table to ensure that each contact has a unique email address.

• CHECK constraint: You might use a CHECK constraint to ensure that the data in a field meets certain conditions. For example, you could use a CHECK constraint to ensure that the "deal_probability" field in the "opportunities" table is a value between 0 and 100.

• DEFAULT constraint: You might use a DEFAULT constraint to specify a default value for a field. For example, you could use a DEFAULT constraint on the "stage" field in the "opportunities" table to set the default value to "prospecting"

### SQL Question 4: Analyzing Customer Product Reviews on Etsy

Consider you have access to two tables in Etsy's database, and . The table contains information about each unique customer, including first and last names, along with a unique . The table includes customer reviews, with a , (matching that in the table), submission date (), , and (rating)—they reflect the review left by a customer for a particular product.

Write a SQL query to calculate the average rating () for each product on a monthly basis.

##### Example Input:
user_idfirst_namelast_name
123SarahSmith
265JohnDoe
362LisaNguyen
192MohammedAli
981SamanthaBrown
##### Example Input:
review_iduser_idsubmit_dateproduct_idstars
61711232022-06-08500014
78022652022-06-10698524
52933622022-06-18500013
63521922022-07-26698523
45179812022-07-05698522
##### Example Output:
mthproductavg_stars
6500013.50
6698524.00
7698522.50

The query groups by month and product, then calculates the average rating for each grouped category. The output is sorted by month and then by average rating in descending order.

Since joins come up so often during SQL interviews, try an interactive Spotify JOIN SQL question:

### SQL Question 5: What is a SQL constraint?

A UNIQUE constraint ensures that all values in a column are different. It is often used in conjunction with other constraints, such as NOT NULL, to ensure that the data meets certain conditions.

For example, if you had Etsy employee data stored in a database, here's some constraints you'd use:

In the Etsy employee example, the UNIQUE constraint is applied to the "email" field to ensure that each employee has a unique email address. This helps to ensure the integrity of the data in the database and prevents errors that could occur if two employees had the same email address.

### SQL Question 6: Calculate Modified Sales Statistics

Given a table of product sales data, write a SQL query that calculates the modified sales, rounded total sales, and square root of the monthly sales for each product, where modified sales are defined as the absolute difference between the total sales and 25% of the sales. The query should use the ABS(), ROUND(), and SQRT() functions.

##### Example Input:
idproduct_idsale_datesales
11002022-06-0125
21012022-06-0230
31002022-06-0345
41012022-07-1055
51002022-07-1560

In this query, we first group the data by product_id and month_year, then for each group, we use the SQL functions as the following:

• We use to calculate the absolute difference between the total sales () and 25% of the total sales (), defining the modified sales.

• We use to round the total sales ().

• We use to calculate the square root of the total sales ().

This gives us per-product and per-month the modified, rounded, and square-rooted sales figures.

### SQL Question 7: Can you describe the different types of joins in SQL?

In SQL, a join is used to combine rows from different tables based on a shared key or set of keys, resulting in a single merged table of data.

There are four distinct types of JOINs: , , , and .

(INNER) JOIN: Retrieves records that have matching values in both tables involved in the join.

LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.

RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.

FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or right table.

### SQL Question 8: Average Ratings per Month for Products

Etsy, an e-commerce website focused on handmade or vintage items and craft supplies, maintains a rating system where users review and rate the products they purchase. In the table, each row signifies a product review. This table includes columns like , , , , and (out of 5).

The column is of TIMESTAMP type, which means it not only contains the date, but also the exact time a review was submitted.

You are asked to write a SQL query to calculate the average star rating for each product per month. Output should contain the month, product_id, and average rating.

##### Example Input:
review_iduser_idsubmit_dateproduct_idstars
61711232022-06-08 00:00:00500014
78022652022-06-10 00:00:00698524
52933622022-06-18 00:00:00500013
63521922022-07-26 00:00:00698523
45179812022-07-05 00:00:00698522

You can extract the month from the timestamp using the EXTRACT function in PostgreSQL. Then you can group the records by month and product to calculate the average rating. Here is the query:

This SQL query will solve the problem by first segregating the data both by product ID and the month the review was submitted. It averages the star ratings of each group and rounds it to two decimal places. The result list is then ordered first by month (mth) and then by product to ensure a tidy output.

##### Example Output:
mthproductavg_stars
6500013.50
6698524.00
7698522.50

### How To Prepare for the Etsy SQL Interview

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Etsy SQL interview is to solve as many practice SQL interview questions as you can! In addition to solving the earlier Etsy SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like Google, Uber, and Microsoft.

Each interview question has hints to guide you, full answers and most importantly, there's an online SQL code editor so you can right in the browser run your SQL query and have it executed.

To prep for the Etsy SQL interview you can also be wise to practice SQL questions from other tech companies like:

But if your SQL skills are weak, forget about diving straight into solving questions – improve your SQL foundations with this SQL tutorial for Data Scientists & Analysts.

This tutorial covers SQL concepts such as WHERE vs. HAVING and aggregate functions – both of these show up routinely in SQL interviews at Etsy.

### Etsy Data Science Interview Tips

#### What Do Etsy Data Science Interviews Cover?

In addition to SQL interview questions, the other types of problems to practice for the Etsy Data Science Interview are:

#### How To Prepare for Etsy Data Science Interviews?

The best way to prepare for Etsy Data Science interviews is by reading Ace the Data Science Interview. The book's got:

• 201 Interview Questions from companies like Microsoft, Google & Amazon
• A Refresher covering SQL, Product-Sense & ML
• Great Reviews (900+ reviews, 4.5-star rating)