What are common SQL and transformation tasks you handle with Kettle (job and transformations)?
Use the supplied PostgreSQL tables to demonstrate a representative transformation that joins related data, filters records, calculates order totals, aggregates customer results, handles missing values, and assigns a classification.
Output
- Return one row per customer with
customer_id, customer_name, completed_orders, total_sales, average_order_value, and sales_band.
- Include customers without completed orders, order by
total_sales descending and customer_id ascending.
Output details
- Treat only records whose status is exactly
completed as completed orders.
- Calculate each order total as the sum of
quantity multiplied by unit_price across its order items.
- Treat missing order totals as numeric zero for
total_sales and average_order_value.
- Round
average_order_value to exactly two decimal places.
- Classify sales of at least
500 as High, positive sales below 500 as Standard, and zero or negative sales as No sales.
