Prompts

SELECT * FROM users WHERE status = 'active';

SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

SELECT
  c.name AS category,
  SUM(oi.quantity * oi.price) AS total_revenue
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN order_items oi ON p.id = oi.product_id
GROUP BY c.id, c.name
ORDER BY total_revenue DESC;

SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_sales
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2024
GROUP BY month
ORDER BY month;

SELECT
  e.id,
  e.name,
  e.salary,
  d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
)
ORDER BY e.salary DESC;

SELECT
  c.id,
  c.name,
  c.email,
  MAX(o.created_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email
HAVING MAX(o.created_at) < CURRENT_DATE - INTERVAL '90 days'
OR MAX(o.created_at) IS NULL
ORDER BY last_order_date DESC NULLS LAST;

SELECT * FROM users
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY created_at DESC;

SELECT
  DATE_TRUNC('hour', created_at) AS hour,
  COUNT(*) AS registrations
FROM users
WHERE DATE(created_at) = CURRENT_DATE
GROUP BY hour
ORDER BY hour;

The 'users' table stores user account information with the following structure:

Primary Fields:
- id: Unique identifier (auto-incrementing integer)
- email: User's email address (unique, used for authentication)
- name: User's display name

Status & Metadata:
- status: Account status ('active', 'inactive', 'suspended')
- created_at: Account creation timestamp
- updated_at: Last modification timestamp

Relationships:
- Referenced by 'orders' table via user_id foreign key
- Referenced by 'user_preferences' table via user_id foreign key

Indexes:
- Primary key on 'id'
- Unique index on 'email'
- Index on 'status' for filtering active users

The 'created_at' column is a timestamp field that tracks when each record
was first inserted into the table.

Key characteristics:
- Type: TIMESTAMP
- Default: CURRENT_TIMESTAMP (automatically set on insert)
- Nullable: NO (always has a value)
- Timezone: Stored in UTC

Common uses:
- Audit trail for record creation
- Filtering recent records (e.g., "created in last 30 days")
- Sorting records chronologically
- Analytics and reporting on creation trends

The database implements a classic e-commerce order model:

customers → orders (One-to-Many):
- Each customer can have multiple orders
- orders.customer_id references customers.id
- Enforced by foreign key constraint with CASCADE delete

orders → order_items (One-to-Many):
- Each order contains multiple line items
- order_items.order_id references orders.id
- order_items is a junction table linking orders to products

products → order_items (One-to-Many):
- Each product can appear in multiple order line items
- order_items.product_id references products.id
- Stores quantity and price at time of purchase

Data Flow:
1. Customer places order → creates orders record
2. Items added to order → creates order_items records
3. Each order_item links to a product and stores snapshot of price/quantity

This database implements a multi-tenant SaaS application with the following modules:

User Management:
- users: User accounts and authentication
- user_roles: Role assignments (admin, user, viewer)
- user_sessions: Active login sessions

Organization:
- organizations: Tenant/company records
- organization_members: User-to-organization relationships
- organization_settings: Per-tenant configuration

Core Business:
- projects: Main workspace entities
- tasks: Work items within projects
- comments: Discussion threads on tasks
- attachments: File uploads linked to tasks

System:
- audit_logs: Track all user actions
- notifications: User notification queue
- webhooks: External integration endpoints

Design Patterns:
- Soft deletes: deleted_at column for reversible deletions
- Timestamps: created_at/updated_at on all tables
- UUIDs: Primary keys use UUID for distributed systems