Analyze Bookstore Data to Identify Top Payment Methods
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
books
+---------+------------+----------+-------+
| book_id | title | author_id| price |
+---------+------------+----------+-------+
| 1 | SQL 101 | 10 | 29.99 |
| 2 | Python | 11 | 39.99 |
| 3 | ML Guide | 10 | 49.99 |
+---------+------------+----------+-------+
authors
+-----------+-------+--------------------------+
| author_id | name | personal_url |
+-----------+-------+--------------------------+
| 10 | Alice | example.com/alice |
| 11 | Bob | example.com/bob_keyword |
| 12 | Carol | example.com/carol |
+-----------+-------+--------------------------+
transactions
+---------------+---------+-------------+----------+-----------+--------+
| transaction_id| book_id | customer_id | quantity | pay_method| amount |
+---------------+---------+-------------+----------+-----------+--------+
| 1001 | 1 | 501 | 2 | Visa | 59.98 |
| 1002 | 2 | 502 | 1 | PayPal | 39.99 |
| 1003 | 3 | 503 | 1 | Visa | 49.99 |
+---------------+---------+-------------+----------+-----------+--------+
customers
+-------------+-------+------------------------+
| customer_id | name | referred_by_customer_id|
+-------------+-------+------------------------+
| 501 | Dan | NULL |
| 502 | Eve | 501 |
| 503 | Frank | 502 |
+-------------+-------+------------------------+
##### Scenario
Online bookstore analytics for business insights using relational data.
##### Question
Using the four-table bookstore schema, write a query to return the top 5 payment methods by total sales amount.
2) Calculate
(a) the proportion of authors that have zero sales and
(b) the proportion of authors whose personal_url contains a given keyword (e.g., 'keyword').
3) Some customers are referred by other customers (customers.referred_by_customer_id). Find the 5 referrers whose referees purchased books with the highest average price; ignore customers who were not referred by anyone.
##### Hints
Pay attention to LEFT/RIGHT joins, GROUP BY with COUNT(DISTINCT), windowing or ORDER BY + LIMIT, and handling NULL referrers.
Overview: This question evaluates data manipulation and analytical SQL/Python competency, focusing on relational joins, aggregations, string filtering, ranking, and proportion calculations across multiple tables in a bookstore schema.
You are given an online bookstore schema with books, authors, transactions, and customers.
Write one PostgreSQL query that returns a single labeled result table for these analyses:
1. Top 5 payment methods by total sales amount.
2. The proportion of authors with zero sales and the proportion of authors whose personal_url contains the keyword 'keyword'.
3. For customers who were referred by another customer, the top referrers by their referred customers' quantity-weighted average purchased book price.
Return all rows in one output with a result_set column. Columns that do not apply to a given result_set should be NULL.
Tables
authors(author_id INTEGER, name VARCHAR(100), personal_url VARCHAR(255))
customers(customer_id INTEGER, name VARCHAR(100), referred_by_customer_id INTEGER)
books(book_id INTEGER, title VARCHAR(100), author_id INTEGER, price DECIMAL(10,2))
transactions(transaction_id INTEGER, book_id INTEGER, customer_id INTEGER, quantity INTEGER, pay_method VARCHAR(20), amount DECIMAL(10,2))
Hints
- Use left joins from authors to preserve authors with no transactions.
- Weight average book price by purchased quantity.