94 lines
3.2 KiB
Python
94 lines
3.2 KiB
Python
#!/usr/bin/env python3
|
|
"""Find best selling books."""
|
|
|
|
import asyncio
|
|
import os
|
|
from mcp import ClientSession, StdioServerParameters
|
|
from mcp.client.stdio import stdio_client
|
|
|
|
|
|
async def main():
|
|
server_params = StdioServerParameters(
|
|
command="python3",
|
|
args=["pg_mcp_server/server.py"],
|
|
env=os.environ.copy(),
|
|
)
|
|
|
|
async with stdio_client(server_params) as (read, write):
|
|
async with ClientSession(read, write) as session:
|
|
await session.initialize()
|
|
|
|
print("=" * 70)
|
|
print("📚 BEST SELLING BOOKS ANALYSIS")
|
|
print("=" * 70)
|
|
|
|
# Query: Best sellers by quantity sold
|
|
query_qty = """
|
|
SELECT
|
|
p.name as book_name,
|
|
p.price,
|
|
SUM(oi.quantity) as total_sold,
|
|
COUNT(DISTINCT oi.order_id) as orders,
|
|
SUM(oi.quantity * oi.unit_price) as total_revenue
|
|
FROM products p
|
|
JOIN order_items oi ON p.id = oi.product_id
|
|
WHERE p.category = 'Books'
|
|
GROUP BY p.id, p.name, p.price
|
|
ORDER BY total_sold DESC
|
|
"""
|
|
|
|
result = await session.call_tool("execute_query", {
|
|
"query": query_qty,
|
|
"limit": 10
|
|
})
|
|
print("\n🏆 By Quantity Sold:")
|
|
print(result.content[0].text)
|
|
|
|
# Query: Best sellers by revenue
|
|
query_revenue = """
|
|
SELECT
|
|
p.name as book_name,
|
|
p.price,
|
|
SUM(oi.quantity) as total_sold,
|
|
SUM(oi.quantity * oi.unit_price) as total_revenue,
|
|
ROUND(AVG(oi.unit_price), 2) as avg_selling_price
|
|
FROM products p
|
|
JOIN order_items oi ON p.id = oi.product_id
|
|
WHERE p.category = 'Books'
|
|
GROUP BY p.id, p.name, p.price
|
|
ORDER BY total_revenue DESC
|
|
"""
|
|
|
|
result = await session.call_tool("execute_query", {
|
|
"query": query_revenue,
|
|
"limit": 10
|
|
})
|
|
print("\n💰 By Revenue:")
|
|
print(result.content[0].text)
|
|
|
|
# Overall books category stats
|
|
query_stats = """
|
|
SELECT
|
|
p.category,
|
|
COUNT(DISTINCT p.id) as num_products,
|
|
SUM(oi.quantity) as total_units_sold,
|
|
SUM(oi.quantity * oi.unit_price) as total_revenue,
|
|
ROUND(AVG(oi.quantity), 1) as avg_qty_per_order
|
|
FROM products p
|
|
JOIN order_items oi ON p.id = oi.product_id
|
|
GROUP BY p.category
|
|
ORDER BY total_revenue DESC
|
|
"""
|
|
|
|
result = await session.call_tool("execute_query", {
|
|
"query": query_stats,
|
|
"limit": 10
|
|
})
|
|
print("\n📊 Category Comparison:")
|
|
print(result.content[0].text)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
os.environ["PG_CONNECTION_STRING"] = "postgresql://postgres:demo@localhost:5432/shop"
|
|
asyncio.run(main())
|