Is there a better alternative to writing multiple subqueries in SQL?

Question

I am querying on Redash to extract an aggregate list of parking places with a pricing grid. The listing table consists of parking details (Name, address, link) and multiple pricing columns depending on duration of stay.

I am using two tables (park and offers). The offers table has three fields, park_id, price and duration. Each park_id may have prices corresponding to a certain duration of stay. As an example, park #1 will charge $2 for 3600 seconds, $30 for 86400 seconds and so on.

I have written separate subqueries to obtain these prices per duration, starting from 1 hour to 24 hours(1 day), then continuing on to 31 days and then for 93 days, 186 days and 372 days. These are 57 subqueries in total.

The resulting table has 57 columns of prices (price1h, price2h….price1d, price 2d….. price31d, price93d, price186d, price372d).

Conditions: There are two important conditions I must use to get the price. One is offers.duration = 3600/7200 etc, and then I filter for offers which package type and not subscription type. Finally, AND offers.park_id = parks.id links the two key fields from the tables park and offers.

The issue is that I am repeating this subquery multiple times for different durations, the execution is painfully slow and exceeds the ‘Redash query execution time limit’

A snippet of the query is below:

SELECT DISTINCT parkid AS ID,
park_name AS Name,
park_address AS full_address,
park_url_description AS park_url,

      (SELECT offers.price
          FROM offers
          WHERE offers.type = 'package'
            AND offers.duration = 3600
            AND offers.park_id = park.parkid) AS price1h,
         
           (SELECT offers.price
           FROM offers
           WHERE offers.type = 'package'
           AND offers.duration = 7200
           AND offers.park_id = park.parkid) AS price2h,       

          (SELECT offers.price
          FROM offers
          WHERE offers.type = 'package'
          AND offers.duration = 10800
          AND offers.park_id = park.parkid) AS price3h,       
          …….. /* and so on */
FROM park
LEFT JOIN offers ON park.parkid = offers.park_id
GROUP BY id, Name, full_address, park_url

And the result table be something like this: https://i.stack.imgur.com/0opp0.png

Can anyone suggest a better alternative to get these prices? I tried using CTE method but I am not experienced enough so I could not come up with a solution (I'm an intern and still learning.) Thank you in advance.

Provide CREATE TABLE for both tables, sample data as INSERT INTO and desired output for this data. — Akina, Nov 16 '20 at 13:35

slaakso · Accepted Answer · 2020-11-16T14:49:18.297

0

Assuming you have one offer per park+duration, you could do it via conditional use of aggregate function:

SELECT 
  p.parkid AS ID,
  p.park_name AS Name,
  p.park_address AS full_address,
  p.park_url_description AS park_url,
  max(if (o.duration = 3600, o.price, null)) as price1h,
  max(if (o.duration = 7200, o.price, null)) as price2h,
  max(if (o.duration = 10800, o.price, null)) as price3h
FROM park p
  LEFT JOIN offers o ON p.parkid = o.park_id and o.type = 'package'
  AND o.duration in (3600, 7200, 10800)
GROUP BY p.parkid, p.park_name, p.park_address, p.park_url_description

See dbfiddle

edited Nov 16 '20 at 14:49

answered Nov 16 '20 at 14:16

slaakso

6,535
2
13
26

We actually have a price grid for each park, where price corresponds to the duration it is booked for. But I liked your idea of declaring each price with an IF statement. I will give it a try. – Keedaman Nov 16 '20 at 14:28
Do you have multiple different prices for each park - duration combo or only one for each? – slaakso Nov 16 '20 at 14:30
The first one (if I interpreted your question correctly) - a duration combo. Each park priced differently for a set duration. As an example, if park #1 charges $2 for 1h, $20 for 24h, it's different to park #2 which charges $5 for 1h and $50 for 24h. I hope this answers your question. – Keedaman Nov 16 '20 at 14:37
Was asking if park #1 could have multiple prices for 1h. If not, the above query should do what you want and probably lot quicker than the 57 subqueries. – slaakso Nov 16 '20 at 14:45
I get it, so sorry. Parks don't have multiple prices for each duration. Cheers. I will give it a go. – Keedaman Nov 16 '20 at 14:48

Is there a better alternative to writing multiple subqueries in SQL?

1 Answers1