Aggregate columns based on different conditions?

Question

I have a Teradata query that generates:

customer | order | amount | days_ago
123      | 1     | 50     | 2
123      | 1     | 50     | 7
123      | 2     | 10     | 19
123      | 3     | 100    | 35
234      | 4     | 20     | 20
234      | 5     | 10     | 10

With performance in mind, what’s the most efficient way to produce an output per customer where orders is the number of distinct orders a customer had within the last 30 days and total is the sum of the amount of the distinct orders regardless of how many days ago the order was placed?

Desired output:

customer | orders | total
123      | 2      | 160
234      | 2      | 30

Answer 1

Given your rules, maybe it takes two steps - de-duplicate first then aggregate:

SELECT customer, 
SUM(CASE WHEN days_ago <=30 THEN 1 ELSE 0 END) AS orders,
SUM(amount) AS total
 FROM
(SELECT customer, order, MAX-or-MIN(amount) AS amount, MIN-or-MAX(days_ago) AS days_ago
FROM your_relation
GROUP BY 1, 2) AS DistinctCustOrder
GROUP BY 1;