sql - PostgresSql:Comparing two tables and obtaining its result and compare it with third table

Question

TABLE 2 : trip_delivery_sales_lines

+-------+---------------------+------------+----------+------------+-------------+--------+--+
| Sl no |     Order_date      | Partner_id | Route_id | Product_id | Product qty | amount |  |
+-------+---------------------+------------+----------+------------+-------------+--------+--+
|     1 | 2020-08-01 04:25:35 |      34567 |      152 |        432 |           2 |    100 |  |
|     2 | 2021-09-11 02:25:35 |      34572 |      130 |        312 |           4 |    150 |  |
|     3 | 2020-05-10 04:25:35 |      34567 |      152 |        432 |           3 |    123 |  |
|     4 | 2021-02-16 01:10:35 |      34572 |      130 |        432 |           5 |    123 |  |
|     5 | 2020-02-19 01:10:35 |      34567 |      152 |        432 |           2 |    600 |  |
|     6 | 2021-03-20 01:10:35 |      34569 |      152 |        123 |           1 |    123 |  |
|     7 | 2021-04-23 01:10:35 |      34570 |      152 |        432 |           4 |    200 |  |
|     8 | 2021-07-08 01:10:35 |      34567 |      152 |        432 |           3 |     32 |  |
|     9 | 2019-06-28 01:10:35 |      34570 |      152 |        432 |           2 |    100 |  |
|    10 | 2018-11-14 01:10:35 |      34570 |      152 |        432 |           5 |     20 |  |
|       |                     |            |          |            |             |        |  |
+-------+---------------------+------------+----------+------------+-------------+--------+--+

From Table 2 : we had to find partners in route=152 and find the sum of product_qty of the last 2 sale [can be selected by desc order_date]

. We can find its result in table 3.

34567 – Serial number [ 1,8] 
34570 – Serial number [ 7,9] 
34569 – Serial number [6] 

TABLE 3 : RESULT OBTAINED FROM TABLE 1,2

+------------+-------+
| Partner_id | count |
+------------+-------+
|      34567 |     5 |
|      34569 |     1 |
|      34570 |     6 |
|            |       |
+------------+-------+

From table 4 we want to find the above partner_ids leaf count

TABLE 4 :coupon_leaf

+------------+-------+
| Partner_id | Leaf  |
+------------+-------+
|      34567 | XYZ1  |
|      34569 | XYZ2  |
|      34569 | DDHC  |
|      34567 | DVDV  |
|      34570 | DVFDV |
|      34576 | FVFV  |
|      34567 | FVV   |
|            |       |
+------------+-------+

From that we can find result as:

34567 – 3
34569-2
34570 -1

TABLE 5: result obtained from TABLE 4

+------------+-------+
| Partner_id | count |
+------------+-------+
|      34567 |     3 |
|      34569 |     2 |
|      34570 |     1 |
|            |       |
+------------+-------+

Now we want compare table 3 and 5

If partner_id count [table 3] > partner_id count [table 4]
        Print partner_id

I want a single query to do all these operation

distinct partner_id can be found by: fROM TABLE 1

SELECT  DISTINCT partner_id 
FROM trip_delivery_sales ts 
WHERE ts.route_id='152' 
GROUP BY ts.partner_id

Answer 1

This answers the original version of the problem.

You seem to want to compare totals after aggregating tables 2 and 3. I don't know what table1 is for. It doesn't seem to do anything.

So:

select *
from (select partner_id, sum(quantity) as sum_quantity
      from (select tdsl.*,
                   row_number() over (partition by t2.partner_id order by order_date) as seqnum
            from trip_delivery_sales_lines tdsl
           ) tdsl
      where seqnum <= 2
      group by tdsl.partner_id
     ) tdsl left join
     (select cl.partner_id, count(*) as leaf_cnt
      from coupon_leaf cl
      group by cl.partner_id
     ) cl
     on cl.partner_id = tdsl.partner_id
where leaf_cnt is null or sum_quantity > leaf_cnt