Peloton Interactive Data Analyst Interview Questions | Glassdoor

Peloton Interactive Data Analyst Interview Questions

Interviews at Peloton Interactive

1 Interview Review

Experience

Experience
0%
100%
0%

Getting an Interview

Getting an Interview
100%

Difficulty

0.0
Average

Difficulty

Hard
Average
Easy

 

Data Analyst Interview

Anonymous Interview Candidate
No Offer
Neutral Experience

Application

I applied online. I interviewed at Peloton Interactive in November 2017.

Interview

I was sent SQL exercises before a phone screen. I have posted the SQL questions below.

AFTER I completed the exercises, I was told they were looking to hire candidates with more experience.

Interview Questions

  • Assume you have the three database tables below representing a simplified e-commerce system.

    CREATE TABLE customer (
 customer_id INT PRIMARY KEY,
 first_name VARCHAR(255),
 last_name VARCHAR(255),
 email VARCHAR(255),
 created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE TABLE purchase (
 purchase_id INT PRIMARY KEY,
 purchase_time TIMESTAMP WITH TIME ZONE NOT NULL,
 customer_id INT NOT NULL,
 FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

CREATE TABLE purchase_item (
 purchase_item_id INT PRIMARY KEY,
 purchase_id INT NOT NULL,
 sku VARCHAR(255),
 quantity INT NOT NULL,
 total_amount_paid DECIMAL(10,2) NOT NULL,
 FOREIGN KEY (purchase_id) REFERENCES purchase(purchase_id)
);
    You will be asked to write SQL to answer various questions about the data.

    Exercises
    1. Write the SQL to generate a report of the total quantity and amount paid for each purchase. The result should have the following columns. (Difficulty: Easy)
    customer_id
    first_name
    last_name
    email
    purchase_id
    purchase_time
    total_quantity
    total_amount_paid

    2. Write the SQL to generate a report of customers who have made more than 1 purchase, sorted by the total number of purchases in descending order. The result should have the following columns. (Difficulty: Medium)
    customer_id
    first_name
    last_name
    email
    number_of_purchases

    3. Write the SQL to generate a report of the number of customers who have purchased each sku. Any skus with no purchases don't need to be included in the report. The result should have the resulting columns. (Difficulty: Medium)
    sku
    total_number_of_customers

    4. Write the SQL to generate a report of all customers and the total amount they’ve spent. Users who haven’t made any purchases should still be included in the report. The result should have the following columns. (Difficulty: Medium)
    customer_id
    first_name
    last_name
    email
    total_amount_paid

    5. Write the SQL to show the some basic statistics about two types of purchases: those that include a bike (has a purchase_item with sku = ‘bike’) and those that do not. For these two cases, we’re interested in knowing how many purchases there are, the average amount paid across those purchases (known as AOV for “average order value”), and the average number of items in each purchase. The result should be two rows with the following columns. (Difficulty: Hard)
    purchase_has_bike (boolean)
    num_purchases
    avg_amount_paid
    avg_number_of_items

    6. Write the SQL to generate a report of the most recent purchase made by each customer. You don’t need to include customers who haven’t made a purchase. The result should have the following columns. (Hint: you can assume the underlying database supports window functions. But it is still possible without window functions.) (Difficulty: Hard)
    customer_id
    first_name
    last_name
    email
    purchase_id
    purchase_time
    total_quantity
    total_amount_paid   Answer Question
Don't Miss Out On a Job You Love
Upload a resume to easily apply to jobs from anywhere. It's simple to set up.