Q1 .
*Invoice Management System Database Design & Query Challenge*
*Scenario:*
Your company is developing an invoice management system to track sales, customers, and
product details. You've been tasked with designing the database schema and writing SQL
queries to extract valuable information.
*SQL Query Tasks:*
1. *Retrieve All Invoices:* Write a query to fetch all invoice details, including customer name
and total amount.
2. *Invoice Details:* Write a query to retrieve the details of a specific invoice (by ID), including
all items purchased, their quantities, and line totals.
3. *Customer Purchase History:* Write a query to display the purchase history of a particular
customer, showing all invoices, dates, and total amounts.
4. *Top Selling Products:* Write a query to identify the top 5 best-selling products based on the
quantity sold across all invoices.
5. *Monthly Sales Report:* Write a query to generate a monthly sales report, summarizing total
sales amounts for each month of a given year.
*Additional Considerations (Optional):*
* How would you handle discounts or taxes in your database design?
* Could you design a query to calculate the average order value for a specific time period?
* How would you accommodate multiple shipping addresses for a single customer?
*Instructions:*
1. Design the database schema, clearly defining tables, columns, data types, and relationships
(primary/foreign keys).
2. Provide the SQL queries for each of the tasks listed above. Assume you're using a standard
relational database like MySQL, PostgreSQL, or SQL Server.
[Link] Processing System Database Design & Query Challenge
Scenario:
Your company is building a comprehensive payment processing system to handle various
transaction types, payment methods, and customer interactions. You are responsible for
designing the underlying database schema and crafting SQL queries to extract key insights.
SQL Query Tasks:
Transaction History: Write a query to retrieve a customer's complete transaction history,
including dates, amounts, payment methods, and status.
Successful Payments: Write a query to calculate the total amount of successful payments for a
given month.
Payment Method Usage: Write a query to determine the most popular payment method used by
customers.
Subscription Revenue: Write a query to calculate the total revenue generated from active
subscriptions in the current month.
Dispute Resolution Time: Write a query to find the average time taken to resolve disputes.
Additional Considerations (Optional):
How would you design tables to store details specific to different payment methods (e.g., credit
card numbers, bank account details)?
Could you design a query to identify customers with multiple failed payment attempts in a short
period?
How would you incorporate support for refunds or partial payments into your database design?
Instructions:
Design the database schema, clearly defining tables, columns, data types, and relationships
(primary/foreign keys).
Hint : -
Database Design Requirements:
Customer Table: Store customer information (ID, Name, Email, Payment Methods).
Payment Method Table: Store details about supported payment methods (ID, Type [Credit Card,
Bank Transfer, etc.], Provider).
Transaction Table: Store transaction records (ID, Customer ID, Payment Method ID, Amount,
Date, Status [Pending, Success, Failed]).
Subscription Table: Store details for recurring payments (ID, Customer ID, Payment Method ID,
Amount, Start Date, End Date, Frequency).
Dispute Table: Store records of transaction disputes (ID, Transaction ID, Reason, Status [Open,
Resolved]).