Business Context
Sigmoid's retail analytics platform needs to identify the second-highest sales transaction within each store category for category-level performance reviews.
Task
Write a PostgreSQL query that returns the second-highest distinct transaction amount for every retail category represented by a store. If multiple transactions share the second-highest amount, return all of them.
Requirements
- Join categories, stores, and sales transactions.
- Rank non-null transaction amounts independently within each category.
- Return transactions with rank 2, ordered by category name and transaction ID.
- Exclude categories with fewer than two distinct transaction amounts.
