name
sales_analytics
description
Database schema and business logic for sales data analysis including customers, orders, and revenue.
Sales Analytics Schema
Tables
customers
customer_id (PRIMARY KEY)
name
email
signup_date
status (active/inactive)
customer_tier (bronze/silver/gold/platinum)
orders
order_id (PRIMARY KEY)
customer_id (FOREIGN KEY -> customers)
order_date
status (pending/completed/cancelled/refunded)
total_amount
sales_region (north/south/east/west)
order_items
item_id (PRIMARY KEY)
order_id (FOREIGN KEY -> orders)
product_id
quantity
unit_price
discount_percent
Business Logic
Active customers
: status = 'active' AND signup_date <= CURRENT_DATE - INTERVAL '90 days'
Revenue calculation
: Only count orders with status = 'completed'. Use total_amount from orders table.
High-value orders
: Orders with total_amount > 1000
Example Query
-- Get top 10 customers by revenue in the last quarter
SELECT
c.customer_id, c.name, c.customer_tier,
SUM
(o.total_amount)
as
total_revenue
FROM
customers c
JOIN
orders o
ON
c.customer_id
Skills relacionados
o.customer_id
WHERE
o.status
'completed'
AND
o.order_date
=
CURRENT_DATE
INTERVAL
'3 months'
GROUP
BY
c.customer_id, c.name, c.customer_tier
ORDER
BY
total_revenue
DESC
LIMIT
10
;