PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Nested conditions within WHERE clause

I have a table app_order with columns price (a number) and side (side can be either 'up' or 'down').

I need to write a postgreSQL condition within a WHERE clause that filters app_order entries for which price is both:

  1. bigger than the average of 2 prices (max price with side=’up’ and min price with side=’down’) – 1 AND

  2. smaller than the average of 2 prices (max price with side=’up’ and min price with side=’down’) + 1

How can I nest these conditions to achieve this?

At the end, I need a query similar to this:

SELECT
  market AS "MARKET",
  SUM(size*price) AS "SUM"
FROM
  app_order
WHERE
  --Missing condition here
GROUP BY
  market
ORDER BY
  market ASC
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Ended up finding the solution (thanks anyway!):

    SELECT
  market AS "MARKET",
  SUM(size * price) AS "SUM"
FROM
  app_order
WHERE
  price > (
    (SELECT MAX(price) FROM app_order WHERE side = 'up') + 
    (SELECT MIN(price) FROM app_order WHERE side = 'down')
  ) / 2 - 1
  AND
  price < (
    (SELECT MAX(price) FROM app_order WHERE side = 'up') + 
    (SELECT MIN(price) FROM app_order WHERE side = 'down')
  ) / 2 + 1
GROUP BY
  market
ORDER BY
  market ASC;

  2. 0

    This is how I understood the question.

    Using a CTE (or a subquery; pick the one you prefer), find max "up" and min "down" price. Then join the original app_order table with a CTE on conditions you specified.

    Something like this:

    WITH
   minimax
   AS
      (SELECT MAX (CASE WHEN side = 'up' THEN price ELSE NULL END) max_up_price,
              MIN (CASE WHEN side = 'down' THEN price ELSE NULL END) min_down_price
         FROM app_order)
  SELECT o.market, SUM (o.size * o.price) sum_size_price
    FROM app_order o
         JOIN minimaya
            ON     o.price > (a.max_up_price + a.min_down_price) / 2 - 1
               AND o.price < (a.max_up_price + a.min_down_price) / 2 + 1
GROUP BY o.market            ---------------------------------------                 
                             this is the "average of two prices" 
                             you mentioned
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search