Quick Overview

This question evaluates relational database design and data-manipulation competencies, including schema modeling and normalization, SQL-based association analysis for co-purchases, and vectorized data transformation using Python/Pandas.

Design Scalable Database and Analyze E-commerce Data

Company: Google

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

transactions +-----------+----------+------------+------------+ | user_id | order_id | product_id | order_time | +-----------+----------+------------+------------+ | 101 | 5001 | 23 | 2023-09-01 | | 101 | 5001 | 45 | 2023-09-01 | | 102 | 5002 | 23 | 2023-09-01 | | 103 | 5003 | 67 | 2023-09-02 | | 103 | 5003 | 45 | 2023-09-02 | +-----------+----------+------------+------------+ ##### Scenario A video-streaming start-up needs a scalable database; later you are asked to solve quick data-ops questions for an e-commerce team. ##### Question Design a relational database for the video company: what tables and key columns would you create and how are they related? Write an SQL query that returns the pair of products most frequently purchased together. Using Python/Pandas, add a new column to a DataFrame based on logical conditions from existing columns. ##### Hints Think normalization, primary/foreign keys, group by order_id, value_counts or window functions, and vectorized Pandas operations.

Overview: This question evaluates relational database design and data-manipulation competencies, including schema modeling and normalization, SQL-based association analysis for co-purchases, and vectorized data transformation using Python/Pandas.

Design video platform schema

Create SQL DDL statements to design a normalized relational database schema for a video streaming company. Define tables, primary keys, and foreign key relationships that capture users, subscription plans, subscriptions, profiles, videos, video assets, genres, video-genre mappings, devices, views, payments, and e-commerce transactions.

Tables

users(user_id INTEGER, email VARCHAR(255), created_at TIMESTAMP, country CHAR(2), status VARCHAR(20))

plans(plan_id INTEGER, name VARCHAR(50), price DECIMAL(10,2), currency CHAR(3), max_screens INTEGER, quality VARCHAR(20))

subscriptions(subscription_id BIGINT, user_id INTEGER, plan_id INTEGER, start_date DATE, end_date DATE, status VARCHAR(20), auto_renew BOOLEAN)

profiles(profile_id BIGINT, user_id INTEGER, profile_name VARCHAR(50), maturity_rating VARCHAR(10))

videos(video_id BIGINT, title VARCHAR(255), type VARCHAR(10), release_date DATE, duration_seconds INTEGER, rating VARCHAR(10))

video_assets(asset_id BIGINT, video_id BIGINT, language VARCHAR(10), resolution VARCHAR(10), codec VARCHAR(20))

genres(genre_id INTEGER, name VARCHAR(50))

video_genres(video_id BIGINT, genre_id INTEGER)

devices(device_id BIGINT, user_id INTEGER, device_type VARCHAR(50), os VARCHAR(50), registered_at TIMESTAMP)

views(view_id BIGINT, profile_id BIGINT, video_id BIGINT, device_id BIGINT, started_at TIMESTAMP, ended_at TIMESTAMP, seconds_watched INTEGER)

payments(payment_id BIGINT, user_id INTEGER, subscription_id BIGINT, amount DECIMAL(10,2), currency CHAR(3), paid_at TIMESTAMP, status VARCHAR(20), method VARCHAR(20))

transactions(user_id INTEGER, order_id INTEGER, product_id INTEGER, order_time DATE)

Hints

  1. Identify many-to-many relationships like videos-to-genres and use junction tables.
  2. Use surrogate primary keys (e.g., user_id, video_id) and composite keys where natural keys exist (e.g., order_id + product_id).

Top co-purchased product pairs

Write an SQL query on the transactions table to return the pair or pairs of products most frequently purchased together within the same order_id. Each pair should contain two different product_id values. If multiple pairs tie for the highest frequency, return all such pairs.

Tables

transactions(user_id INTEGER, order_id INTEGER, product_id INTEGER, order_time DATE)

Hints

  1. De-duplicate items within each order_id before forming pairs, to avoid over-counting.
  2. Self-join the order items on order_id and use a condition like product_id_1 < product_id_2 to avoid reversed/duplicate pairs.

Derive column from conditions

Using SQL on the users table, return each user with an additional derived column named segment: 'US_active' when status = 'active' and country = 'US', 'NonUS_active' when status = 'active' and country <> 'US', and 'inactive' for all other cases.

Tables

users(user_id INTEGER, email VARCHAR(255), created_at TIMESTAMP, country CHAR(2), status VARCHAR(20))

Hints

  1. Use a CASE expression in the SELECT clause to derive the segment value.
  2. Order the CASE conditions from most specific to least specific so they evaluate correctly.

Loading coding console...