CODEWITHSUNDEEP
sqlite
Matthew Ingle
Matthew Ingle

Reputation: 119

SQLite Nested Query for maximum

I'm trying to use DB Browser for SQLite to construct a nested query to determine the SECOND highest priced item purchased by the top 10 spenders. The query I have to pick out the top 10 spenders is:

SELECT user_id, max(item_total), SUM (item_total + shipping_cost -
discounts_applied) AS total_spent 
FROM orders AS o
WHERE payment_reject = "FALSE" 
GROUP BY user_id 
ORDER BY total_spent DESC 
LIMIT 10

This gives the user_id, most expensive item they purchased (not counting shipping or discounts) as well as the total amount they spent on the site.

I was trying to use a nested query to generate a list of the second most expensive items they purchased, but keep getting errors. I've tried

SELECT user_id, MAX(item_total) AS second_highest
  FROM orders
 WHERE item_total < (SELECT user_id, SUM (item_total + shipping_cost -
discounts_applied) AS total_spent 
FROM orders 
WHERE payment_reject = "FALSE" 
GROUP BY user_id 
ORDER BY total_spent DESC 
LIMIT 10)
group by user_id

I keep getting a row value misused error. Does anyone have pointers on this nested query or know of another way to find the second highest item purchased from within the group found in the first query? Thanks!

Upvotes: 0

Views: 388

Answers (1)

Shawn
Shawn

Reputation: 52589

(Note: The following assumes you're using Sqlite 3.25 or newer since it uses window functions).

This will return the second-largest item_total for each user_id without duplicates:

WITH ranked AS
 (SELECT DISTINCT user_id, item_total
       , dense_rank() OVER (PARTITION BY user_id ORDER BY item_total DESC) AS ranking
  FROM orders)
SELECT user_id, item_total FROM ranked WHERE ranking = 2;

You can combine it with your original query with something like:

WITH ranked AS
 (SELECT DISTINCT user_id, item_total
       , dense_rank() OVER (PARTITION BY user_id ORDER BY item_total DESC) AS ranking
  FROM orders),
 totals AS
 (SELECT user_id
       , sum (item_total + shipping_cost - discounts_applied) AS total_spent 
  FROM orders
  WHERE payment_reject = 0 
  GROUP BY user_id)
SELECT t.user_id, r.item_total, t.total_spent
FROM totals AS t
JOIN ranked AS r ON t.user_id = r.user_id
WHERE r.ranking = 2
ORDER BY t.total_spent DESC, t.user_id
LIMIT 10;

Okay, after fixing your table definition to better reflect the values being stored in it and the stated problem, and fixing the data and adding to it so you can actually get results, plus an optional but useful index like so:

CREATE TABLE orders (order_id INTEGER PRIMARY KEY
                   , user_id INTEGER
                   , item_total REAL
                   , shipping_cost NUMERIC
                   , discounts_applied NUMERIC
                   , payment_reject INTEGER);
INSERT INTO orders(user_id, item_total, shipping_cost, discounts_applied
                 , payment_reject) VALUES (9852,60.69,10,0,FALSE),
 (2784,123.91,15,0,FALSE), (1619,119.75,15,0,FALSE), (9725,151.92,15,0,FALSE),
 (8892,153.27,15,0,FALSE), (7105,156.86,25,0,FALSE), (4345,136.09,15,0,FALSE),
 (7779,134.93,15,0,FALSE), (3874,157.27,15,0,FALSE), (5102,108.3,10,0,FALSE),
 (3098,59.97,10,0,FALSE),  (6584,124.92,15,0,FALSE), (5136,111.06,10,0,FALSE),
 (1869,113.44,20,0,FALSE), (3830,129.63,15,0,FALSE), (9852,70.69,10,0,FALSE),
 (2784,134.91,15,0,FALSE), (1619,129.75,15,0,FALSE), (9725,161.92,15,0,FALSE),
 (8892,163.27,15,0,FALSE), (7105,166.86,25,0,FALSE), (4345,146.09,15,0,FALSE),
 (7779,144.93,15,0,FALSE), (3874,167.27,15,0,FALSE), (5102,118.3,10,0,FALSE),
 (3098,69.97,10,0,FALSE),  (6584,134.92,15,0,FALSE), (5136,121.06,10,0,FALSE), 
 (1869,123.44,20,0,FALSE), (3830,139.63,15,0,FALSE);
CREATE INDEX orders_idx_1 ON orders(user_id, item_total DESC);

the above query will give:

user_id     item_total  total_spent
----------  ----------  -----------
7105        156.86      373.72     
3874        157.27      354.54     
8892        153.27      346.54     
9725        151.92      343.84     
4345        136.09      312.18     
7779        134.93      309.86     
3830        129.63      299.26     
6584        124.92      289.84     
2784        123.91      288.82     
1619        119.75      279.5

(If you get a syntax error from the query now, it's because you're using an old version of sqlite that doesn't support window functions.)

Upvotes: 1

Related Questions