Group Sold Products
ASKED IN INTERVIEWShopify
Problem Statement
Shopify merchants need a daily summary of their sales performance. You are required to generate a report that shows, for each specific date, the total number of unique products sold and their names.
The product names should be unique for each date, sorted alphabetically, and separated by a comma.
Rules:
- Output columns: sell_date, num_sold, products.
- num_sold represents the count of unique products sold on that date.
- products represents a comma-separated string of unique product names, sorted alphabetically.
- Results must be sorted by sell_date in ascending order.
Tests your understanding of
Aggregation, String Functions and Grouping
Input Tables
activities
| sell_date(DATE) | product(VARCHAR) |
|---|---|
| 2020-05-30 | Headphones |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basket |
| 2020-06-01 | Bible |
| 2020-06-01 | Pencil |
| 2020-05-30 | T-Shirt |
product_categories
| product(VARCHAR) | category(VARCHAR) |
|---|---|
| Headphones | Electronics |
| Pencil | Stationery |
| Mask | Health |
sales_reps
| rep_id(INTEGER) | rep_name(VARCHAR) |
|---|---|
| 1 | John Doe |
| 2 | Jane Smith |
Expected Output
| sell_date(DATE) | num_sold(INTEGER) | products(VARCHAR) |
|---|---|---|
| 2020-05-30 | 3 | Basket,Headphones,T-Shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
Tags
EasyASKED IN INTERVIEWAggregationString FunctionsGrouping
10-15 min
68%