Task
You are given expense data across multiple business units and vendors. Write a PostgreSQL query that segments expenses by expense category and vendor, then returns the categories with the highest total spend and the share of spend coming from reimbursable expenses. Use the Rippling Expenses records to help surface cost-saving opportunities.
Schema
| table | column | type | description |
|---|
| expenses | expense_id | INT | Primary key for each expense |
| expenses | employee_id | INT | Employee who submitted the expense |
| expenses | vendor_id | INT | Vendor used for the purchase |
| expenses | expense_date | DATE | Date the expense was incurred |
| expenses | category | VARCHAR(100) | Expense category such as travel or software |
| expenses | amount | DECIMAL(10,2) | Expense amount |
| expenses | reimbursable | BOOLEAN | Whether the expense is reimbursable |
| employees | employee_id | INT | Primary key for each employee |
| employees | department | VARCHAR(100) | Employee department |
| employees | location | VARCHAR(100) | Employee location |
| vendors | vendor_id | INT | Primary key for each vendor |
| vendors | vendor_name | VARCHAR(255) | Vendor name |
| vendors | vendor_type | VARCHAR(100) | Vendor type such as airline or SaaS |
Sample data
expenses
| expense_id | employee_id | vendor_id | expense_date | category | amount | reimbursable |
|---|
| 1 | 101 | 1 | 2024-01-03 | Travel | 1200.00 | true |
| 2 | 102 | 2 | 2024-01-04 | Software | 300.00 | false |
| 3 | 103 | 3 | 2024-01-05 | Meals | 85.50 | true |
| 4 | 101 | 4 | 2024-01-06 | Travel | 450.00 | true |
| 5 | 104 | 2 | 2024-01-07 | Software | 250.00 | false |
vendors
| vendor_id | vendor_name | vendor_type |
|---|
| 1 | SkyJet | Airline |
| 2 | CloudDesk | SaaS |
| 3 | BentoBox | Catering |
| 4 | MetroRide | Ground Transport |
employees
| employee_id | department | location |
|---|
| 101 | Sales | New York |
| 102 | Engineering | San Francisco |
| 103 | Sales | Chicago |
| 104 | Finance | Austin |
Expected output
| category | vendor_name | vendor_type | total_spend | reimbursable_spend | reimbursable_share |
|---|
| Travel | SkyJet | Airline | 1200.00 | 1200.00 | 1.00 |
| Travel | MetroRide | Ground Transport | 450.00 | 450.00 | 1.00 |
| Software | CloudDesk | SaaS | 550.00 | 0.00 | 0.00 |
| Meals | BentoBox | Catering | 85.50 | 85.50 | 1.00 |
