I'm trying to write a query in Athena where I get a list of unique_usage_ids in which the unique_usage_start_date is atleast 1 month before the contract_sign_date
I know how to write a query to find one particular instance of what I'm looking for:
SELECT customer_id, unique_usage_id
FROM customer_data
WHERE customer_id = '12345'
AND unique_usage_start_date < (SELECT contract_sign_date - interval '1' month
FROM contract_data WHERE customer_id = '12345)
The above query runs and I get the desired result for customer_id '12345':
customer_id | unique_usage_id |
---|---|
12345 | ABGSHDVBAHJD |
12345 | NNSJABDGIAGIQ |
12345 | PQSAUIDHUIADH |
How do I expand this query to get all cases of customer_id and unique_usage_id where unique_usage_start_date is atleast 1 month before the contract_sign_date
If I remove the clauses customer_id where clauses:
SELECT customer_id, unique_usage_id
FROM customer_data
WHERE unique_usage_start_date < (SELECT contract_sign_date - interval '1' month
FROM contract_data)
The query won't run. I get the error: SUBQUERY_MULTIPLE_ROWS: Scalar sub-query has returned multiple rows.
Each customer_id has their own distinct contract_sign_date and unique_usage_start_date. I'm trying to find a list of all customer_ids and unique_usage_ids that meet the criteria in the subquery. I get my desired result when I plug in an individual customer_id but I'd like to get the full list across the database.
Do a join and then filter without using a sub-query.
Something like:
SELECT cusd.customer_id,
cusd.unique_usage_id
FROM customer_data cusd
JOIN contract_data cond on cond.customer_id = cusd.customer_id
WHERE cusd.unique_usage_start_date < (cond.contract_sign_date - interval '1' month)