Argthtjtr

I have a table with the columns ID, ad_id, amount_time, which shows the ID of users, the ad that they saw, and how much time they seen it. Here is an example of the data:

table name: ads



ID | ad_id | amount_time

 1      2        600            

 1      3        300

 3      1        400

 1      3        100

 1      1        700

We want the result to show the difference between max and 2nd max of amount_time, grouped by ID and ad_id

So the result is

ID |ad_id | diff_amount_time

 1    3      200

I can get the max and second_max of the total table by executing:

select

(SELECT MAX(amount_time) FROM ads) maxtime,

(SELECT MAX(amount_time) FROM ads

WHERE amount_time NOT IN (SELECT MAX(amount_time) FROM ads )) as 

second_max_time

However, I am having trouble incorporating the group by clause in order to get my result. I know there's a way to incorporate order by and limit 2 to get the max and and second max, but this is computationally expensive and want to know if there is another solution without ordering the amount_time column.

In MySQL 8.0.2+, simplest and possibly most performant way would be to use Window Functions.

We will use Row_Number() function, which will determine the row number values within a combination of ID and ad_id. Row number will be based on descending order amount_time value. So the highest amount_time value will have row number of 1, and the second highest would have row number of 2.

Now, we will use this result-set as a Derived Table, and aggregate (GROUP BY) on ID and ad_id. We can use conditional CASE .. WHEN expressions, to determine the difference between the highest and second highest value within every group.

SELECT

  dt.ID, 

  dt.ad_id, 

  (MAX(CASE WHEN dt.row_no = 1 THEN dt.amount_time END) - 

  MAX(CASE WHEN dt.row_no = 2 THEN dt.amount_time END)) AS diff_amount_time

FROM 

(

 SELECT 

   ID, 

   ad_id, 

   amount_time, 

   ROW_NUMBER() OVER (PARTITION BY CONCAT(ID, '-', ad_id)

                      ORDER BY amount_desc) AS row_no

 FROM ads 

) AS dt 

GROUP BY dt.ID, dt.ad_id 

-- to remove cases where there is no second highest

-- when there is no second highest amount, then the difference will be null

-- because 5 - null = null

HAVING diff_amount_time IS NOT NULL