Write a query to find the total value of sales and the number of unique paying customers, grouped and sorted in descending order by payment type.
Existing customers can invite other people to sign up to the bookstore. Find the IDs of the top 5 customers, ordered by the average payment per book made by the people they invited.
Find the total number of authors. What percentage of them have a website URL that contains .com, and what percentage never made a sale?
Schema:
books(book_id INT KEY, title VARCHAR, author_id INT, publication_date DATE, category VARCHAR, price DOUBLE)
authors(author_id INT KEY, first_name VARCHAR, last_name VARCHAR, birthday DATE, website_url VARCHAR)
transactions(transaction_id INT KEY, book_id INT, customer_id INT, payment_amount DOUBLE, book_count INT, tax_rate DOUBLE, discount_rate DOUBLE, transaction_date DATE, payment_type VARCHAR)
customers(customer_id INT KEY, first_name VARCHAR, last_name VARCHAR, registration_date DATE, interested_in_categories VARCHAR, is_rewards_member BOOLEAN, invited_by_customer_id INT)This is a bookstore analytics SQL problem focused on grouping, joins, and relationship-based aggregation. The first task requires grouping transactions by payment type and computing total sales and distinct paying customers. The second uses the customer invitation chain to rank inviters by the average payment per book made by their invitees. The third summarizes the authors table by counting total authors, measuring how many have a website URL containing .com, and how many never made a sale.
