Analyze Bookstore Data to Identify Top Payment Methods

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.

Bookstore analytics: payments, author proportions, and referrals

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.

Table authors

column	type	constraints
author_id	INTEGER	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
personal_url	VARCHAR(255)	NOT NULL

Sample data:

author_id	name	personal_url
10	Alice	example.com/alice
11	Bob	example.com/bob_keyword
12	Carol	example.com/carol

Table customers

column	type	constraints
customer_id	INTEGER	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
referred_by_customer_id	INTEGER	FOREIGN KEY REFERENCES customers(customer_id)

Sample data:

customer_id	name	referred_by_customer_id
501	Dan	NULL
502	Eve	501
503	Frank	502

Table books

column	type	constraints
book_id	INTEGER	PRIMARY KEY, NOT NULL
title	VARCHAR(100)	NOT NULL
author_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES authors(author_id)
price	DECIMAL(10,2)	NOT NULL

Sample data:

author_id	book_id	price	title
10	1	29.99	SQL 101
11	2	39.99	Python
10	3	49.99	ML Guide

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
book_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES books(book_id)
customer_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
quantity	INTEGER	NOT NULL
pay_method	VARCHAR(20)	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	book_id	customer_id	pay_method	quantity	transaction_id
59.98	1	501	Visa	2	1001
39.99	2	502	PayPal	1	1002
49.99	3	503	Visa	1	1003

Expected output

Sample output for the single labeled result table.

avg_book_price	pay_method	referrer_id	referrer_name	result_set	total_sales	url_keyword_proportion	zero_sales_proportion
NULL	Visa	NULL	NULL	top_payment_methods	109.97	NULL	NULL
NULL	PayPal	NULL	NULL	top_payment_methods	39.99	NULL	NULL
NULL	NULL	NULL	NULL	author_proportions	NULL	0.3333	0.3333
49.99	NULL	502	Eve	top_referrers_by_avg_book_price	NULL	NULL	NULL
39.99	NULL	501	Dan	top_referrers_by_avg_book_price	NULL	NULL	NULL

Hints

1. Use left joins from authors to preserve authors with no transactions.
2. Weight average book price by purchased quantity.
3. Use UNION ALL with NULL placeholders to produce one flat output.