Conditional return values in SQL-CodePudding

I have started a simple auction system where each row contains the following data:

Type:= BID|WIT
ProductName := Str
User := Str
Value := Decimal
CreatedAt := datetime

*Here WIT means withdraw, aka quit from item auction *

A user can naturally do multiple bid requests, always raising the bid value (this is handled at language level)

I would like to list all top bids from all users but under a condition, only if they are not before a WITHDRAW request for the given item.

For example, given the entries

BID,Doll,John,10,2021-11-26 10:10
BID,Ball,John,12,2021-11-26 10:11
BID,Doll,Mary,12,2021-11-26 10:12
BID,Doll,Alice,13,2021-11-26 10:13
BID,Doll,Mary,14,2021-11-26 10:14
BID,Doll,Alice,17,2021-11-26 10:14
BID,Ball,Mary,14,2021-11-26 10:14
WIT,Doll,John,00,2021-11-26 10:16
BID,Doll,Mary,20,2021-11-26 10:18
BID,Ball,John,15,2021-11-26 10:20

If I magic_sql_query(Doll) I would like to get

BID,Doll,Alice,17,2021-11-26 10:14
BID,Doll,Mary,20,2021-11-26 10:18

Also If I magic_sql_query(Ball) I would like to get:

BID,Ball,Mary,14,2021-11-26 10:14
BID,Ball,John,15,2021-11-26 10:20

How can I do it in a SQL Statement?

CodePudding user response：

You can

use the row_number() windowing function to rank within a group (a group is defined by the username and the product); the latest entry gets rank 1
get rid of all entries whose rank is > 1 (i.e. the user has a later entry for this product)
get rid of all entries of type 'WIT'

    with base as (select
      b.type, 
      b.productname, 
      b.username,
      b.value,
      b.createdat,
      row_number() over (partition by productname, username 
                         order by createdat desc) as rn
      from bids b
      order by productname, username, createdat
    )
    select * from base
    where rn = 1
    and type = 'BID';

SQL Fiddle

CodePudding user response：

To find all bids that have no withdrawal later (by the same user for the same product) you can use a NOT EXITS condition:

select a1.*
where a1.product_name = 'Ball'
  and type = 'BID'
  and not exists (select *
                  from auction a2
                  where a2.product_name = a1.product_name
                    and a2.user_name = a1.user_name
                    and a2.type = 'WIT' 
                    and a2.created_at > a1.created_at)

Now we need to filter out the highest bids per product and user. This can be done using the dense_rank() function.

select type, product_name, user_name, value, created_at
from (
  select a1.*, dense_rank() over (partition by product_name, user_name order by value desc) as rnk
  from auction a1
  where a1.product_name = 'Ball'
    and type = 'BID'
    and not exists (select *
                    from auction a2
                    where a2.product_name = a1.product_name
                      and a2.user_name = a1.user_name
                      and a2.type = 'WIT' 
                      and a2.created_at > a1.created_at)
) t
where rnk = 1
order by created_at;

Online example