databasepostgresqlanalysis

Why does my subquery logic give me a different result?

I got a different answer(4434.21) than the supposed result(4721.14)(ERD and can't figure out what went wrong, question below:

Q: What is the lifetime average amount spent in terms of total_amt_usd, including only the companies that spent more per order, on average, than the average of all orders.

WITH t1 AS (SELECT AVG(o.total_amt_usd) total_avg
FROM orders o),

t2 AS(SELECT a.name account_name,
       a.id account_id,
       AVG(o.total_amt_usd) account_avg
FROM accounts a
JOIN orders o
  ON a.id = o.account_id
GROUP BY 1,2
HAVING AVG(o.total_amt_usd) > (SELECT * 
                              FROM t1)
)

SELECT AVG(o.total_amt_usd) 
FROM orders o
JOIN t2 
  ON t2.account_id = o.account_id

Solution：

SELECT AVG(avg_amt)
FROM (
    SELECT o.account_id,
           AVG(o.total_amt_usd) avg_amt
    FROM orders o 
        GROUP BY 1
        HAVING AVG(o.total_amt_usd) > 
        (SELECT AVG(o.total_amt_usd) avg_all
        FROM orders o)
) t1
Solution

  • En..both query are different. First query gets the average of o.total_amt_usd of orders satisfying the join condition. Second query gets the average of average of o.total_amt_usd of orders satisfying some conditions. you might get same result if you use SELECT AVG(t2.account_avg) FROM orders o JOIN t2 ON t2.account_id = o.account_id.