How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

What difficulty level is this interview question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Meta.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Write SQL for seller and vehicle metrics

Q: Write SQL for seller and vehicle metrics

This question evaluates proficiency in SQL data manipulation, including joins, distinct counts, grouping and aggregation, filtering by date and category, and computing percentage metrics for marketplace analytics.

You are working with a marketplace dataset.

Table 1: `listing_interactions`

buyer_id BIGINT
seller_id BIGINT
interaction_date DATE
product_id BIGINT
listing_interactions INT

Each row represents the number of interactions between a buyer and a seller for a given product on a given day.

Table 2: `dim_all_products`

product_id BIGINT
category VARCHAR
snapshot_date DATE
create_date DATE
country VARCHAR

Assume product_id can be used to join the two tables, and for these questions there is one relevant product record per product_id.

Assume all dates are stored in UTC. For the second question, interpret "created in the last 7 days" as create_date between CURRENT_DATE - INTERVAL '6' DAY and CURRENT_DATE, inclusive.

Write SQL for the following:

Count how many sellers have more than 3 distinct products where the total number of listing interactions for that seller-product pair is greater than 1.
- Output column: seller_count
Among products in the US that were created in the last 7 days , compute the percentage of total listing interactions that come from the vehicle category.
- Define the metric as: 100 * sum(listing_interactions for eligible vehicle products) / sum(listing_interactions for all eligible products)
- Output column: vehicle_interaction_pct

You are working with a marketplace dataset.

Table 1: `listing_interactions`

buyer_id BIGINT
seller_id BIGINT
interaction_date DATE
product_id BIGINT
listing_interactions INT

Each row represents the number of interactions between a buyer and a seller for a given product on a given day.

Table 2: `dim_all_products`

product_id BIGINT
category VARCHAR
snapshot_date DATE
create_date DATE
country VARCHAR

Assume product_id can be used to join the two tables, and for these questions there is one relevant product record per product_id.

Assume all dates are stored in UTC. For the second question, interpret "created in the last 7 days" as create_date between CURRENT_DATE - INTERVAL '6' DAY and CURRENT_DATE, inclusive.

Write SQL for the following:

Count how many sellers have more than 3 distinct products where the total number of listing interactions for that seller-product pair is greater than 1.
- Output column: seller_count
Among products in the US that were created in the last 7 days , compute the percentage of total listing interactions that come from the vehicle category.
- Define the metric as: 100 * sum(listing_interactions for eligible vehicle products) / sum(listing_interactions for all eligible products)
- Output column: vehicle_interaction_pct

Write SQL for seller and vehicle metrics

Quick Overview

Table 1: `listing_interactions`

Table 2: `dim_all_products`

Comments (0)

Write SQL for seller and vehicle metrics

Quick Overview

Table 1: `listing_interactions`

Table 2: `dim_all_products`

Comments (0)

Write SQL for seller and vehicle metrics

Quick Overview