How can I access variables in subqueries in SQL

Question

I have created this query to solve this problem,

SELECT c.ra as Day, round(count(cancelled) / Count(*),2) as "Cancellation Rate" FROM  
(
    select a.Id, a.ra, a.Status as status, b.Status as cancelled FROM 
    (
        select Id, Client_Id, Driver_Id, Status, Request_at as ra from Trips t
            where not exists 
            (
                select 1 from users u
                where (t.Client_Id = u.Users_Id or t.Driver_Id = u.Users_Id)
                and u.banned = 'Yes'
            )
    ) a LEFT JOIN 
    (
        select Id, Client_Id, Driver_Id, Status, Request_at as ra from Trips t
            where not exists 
            (
                select 1 from users u
                where (t.Client_Id = u.Users_Id or t.Driver_Id = u.Users_Id)
                and u.banned = 'Yes'
            ) and (t.Status = 'cancelled_by_driver' or t.status = 'cancelled_by_client')
    ) b on a.Id = b.Id
) c GROUP BY c.ra

To make that query more concise, I changed it into the following, but that doesn't work. So, my question is, what is the right way of making such queries more concise?

SELECT c.ra as Day, round(count(cancelled) / Count(*),2) as "Cancellation Rate" FROM  
(
    select a.Id, a.ra, a.Status as status, b.Status as cancelled FROM 
    (
        select Id, Client_Id, Driver_Id, Status, Request_at as ra from ( 
            select * from Trips t
            where not exists 
            (
                select 1 from users u
                where (t.Client_Id = u.Users_Id or t.Driver_Id = u.Users_Id)
                and u.banned = 'Yes'
            )
        ) t1
    ) a LEFT JOIN 
    (
        select Id, Client_Id, Driver_Id, Status, Request_at as ra from t1
            WHERE (Status = 'cancelled_by_driver' or Status = 'cancelled_by_client')
    ) b on a.Id = b.Id
) c GROUP BY c.ra

Please provide sample data, desired results, and an explanation of what you are trying to accomplish. — Gordon Linoff
– Gordon Linoff, Commented Nov 22, 2020 at 13:05

MetallicPriest · Accepted Answer · 2020-11-22 14:37:16Z

1

If I understand correctly, I would just use conditional aggregation:

SELECT Date(request_at) AS Day, 
       Round(Avg(status IN ( 'cancelled_by_driver', 
                             'cancelled_by_client' )), 2) AS 
       "Cancellation Rate" 
FROM   trips t 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   users u 
                   WHERE  u.users_id IN ( t.client_id, t.driver_id ) 
                          AND u.banned = 'Yes') 
GROUP  BY day;

In your second version t1 is simply not defined. If you wanted to re-use a subquery this way, you would need to use a CTE:

with t1 as (
     . . .
    )
select . . .

Then you could reference t1 multiple times in the query.

edited Nov 22, 2020 at 14:37

MetallicPriest

31.1k59 gold badges216 silver badges389 bronze badges

answered Nov 22, 2020 at 13:09

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MetallicPriest Over a year ago

I see, your solution is correct, but the problem requires round off to 2 digits after the decimal. But of course that can be easily achieved by using the round function. Also its Request_at rather than requested_at.

Gordon Linoff Over a year ago

@MetallicPriest . . . I trust that you can make those changes.

Collectives™ on Stack Overflow

How can I access variables in subqueries in SQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related