sqlamazon-athenaprestotrino

PrestoSQL/Trino - How to query all cases without explicit where clauses in subequery


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.


Solution

  • 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)