Find the third largest spender on our web app's users' data without using LIMIT or OFFSET.

Here is the table:

Create database testing_1;
Use Testing_1;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'users')
BEGIN
  EXEC('
    CREATE TABLE users (
      id INT PRIMARY KEY,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      city VARCHAR(50),
      country VARCHAR(50),
      email VARCHAR(100),
      spend DECIMAL(10, 2)
    );
  ');
END

INSERT INTO users (id, first_name, last_name, city, country, email, spend)
VALUES
  (1, 'John', 'Doe', 'New York', 'USA', 'john.doe@example.com', 100.50),
  (2, 'Jane', 'Smith', 'London', 'UK', 'jane.smith@example.com', 75.20),
  (3, 'David', 'Johnson', 'Los Angeles', 'USA', 'david.johnson@example.com', 200.00),
  (4, 'Emma', 'Williams', 'Sydney', 'Australia', 'emma.williams@example.com', 50.80),
  (5, 'Michael', 'Brown', 'Toronto', 'Canada', 'michael.brown@example.com', 150.75);

SELECT * FROM SYS.TABLES; -- you can also see tables doing this

This was an "easy part" interview question for one of the Data Engineering positions. Well, question was not exactly the same but came somewhat close to this.

select * from users order by spend desc;

If you run the code above, you will see John is the third largest spender. We can use the results to compare our result going forward.

-- Print the third highest spender with using TOP or LIMIT
select * 
from users
order by spend Desc
offset 2 rows
fetch next 1 rows only;

-- Print the third highest spender without using TOP or LIMIT
select piw.* from
(select u.*,
row_number() over (order by u.spend Desc) as row_num
from users as u) as piw
where row_num = 3;

If you notice the two queries here, they give out the same result. However, the second one prints out with an extra column called row_num. We used a where condition with this row_num column which dismisses the need for using the limit and offset or offset ... rows and fetch next ... rows only.