SQL query to create a merged table with varied timestamps and varied column mapping

I am trying to write an complex mySQL query in which there are 2 tables action and revenue what I need is:

1. From auction table take out location, postal code on the basis of user, cat_id, cat and spent and join with revenue table which has revenue column so as that given cat_id, cat and date I can figure out the returns that each ‘postal’ is generating.

Complexities:

User is unique key here

1. In auction table has column ‘spent’ but its populates only when ‘event’ column has ‘show’ but it has ‘cat’ entry. And ‘cat_id’ starts populating at any event except show. So need to map cat_id from ‘cat’ for event ‘show’ to get the spent for that cat_id.

2. The date has to be setup such that while joining the tables the timestamp should be compared for plus minus 10 mins. Right now in my query I have 24 hrs duration

3. Aggregating on postal in desc order to postal giving highest returns

**Auction Table**
dt                  user cat_id cat location  postal     event    spent
2020-11-01 22:12:25  1     0    A      US      X12        Show      2
2020-11-01 22:12:25  1     0    A      US      X12        Show      2 (duplicate also in table)
2020-11-01 22:12:25  1     6    A      US      X12        Mid      null
2020-11-01 22:13:20  2     0    B      UK      L23        Show      2
2020-11-01 22:15:24  2     3    B      UK      L23        End      null

**Revenue table**
dt                     user  cat_id   revenue
2020-11-01 22:14:45     1      6        null
2020-11-01 22:13:20     2      3        3

Want to create final table(by aggregating on revenue for each ‘postal’ area):

location   postal   spend  revenue returns
UK          X12      2       0        0
US          L23      2       3        3/2=1.5  

I have written a query but unable to figure out solution for above mention 3 complexities:

Select s.location, s.postal, s.spend, e.revenue
From revenue e JOIN
     auction s
     on e.user = s.user
where  s.event in ('Mid','End','Show') and 
       TO_DATE(CAST(UNIX_TIMESTAMP(e.dt, 'y-M-d') AS TIMESTAMP)) = TO_DATE(CAST(UNIX_TIMESTAMP(s.dt, 'y-M-d') AS TIMESTAMP)) and
       s.cat_id in ('3') and
       s.cat = 'B'

Any suggestion will be helpful

Answer

This answers the question for MySQL, which is the original tag on the question as well as mentioned in the question.

If I understand correctly, your issue is “joining” within a time frame. You can do what you want using a correlated subquery. Then the rest is aggregation, which I think is:

select location, postal, max(spend), max(revenue)
from (select a.*,
             (select sum(r.revenue)
              from revenue r
              where r.user = a.user and
                    r.dte >= s.dt - interval 10 minute and
                    r.dte <= s.dte + interval 10 minute
             ) as revenue
      from auction a
      where s.event in ('Mid', 'End', 'Show') and 
            s.cat_id in (3) and
            s.cat = 'B'
     ) a
group by location, postal;