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.