Customer Lifetime Value by Segment

Write a SQL query to calculate customer lifetime value by customer segment from transactional data. Treat lifetime value as the sum of completed transaction amounts for each customer. Include customers with no completed transactions, use Unspecified for missing segments, and exclude transactions belonging to unknown customers.

Output

1. One row per customer segment
2. Columns: customer_segment, customer_count, total_lifetime_value, and average_lifetime_value
3. Include all customer segments, order by total_lifetime_value descending, then customer_segment ascending