Dynamic self join

Question

Consider the following model, consisting of one table cars with 3 columns:

class Car(Model):
    owner_id = IntegerField()
    name = CharField()
    power = IntegerField()

The input can be from 1 up to 5 car names (no duplicates). How do you get the owners of all cars (partial match not allowed), up to 5, and sort them by the total horse power?

Here's a graphical representation of what I'd like to achieve based on the data below, also available in this SQL fiddle:

create table cars(owner_id integer, name varchar(21), power integer);
insert into cars values (101,'bmw',300);
insert into cars values (101,'audi',200);
insert into cars values (101,'mercedes',100);
insert into cars values (102,'bmw',250);
insert into cars values (102,'mercedes',400);
insert into cars values (103,'bmw',200);
insert into cars values (103,'audi',100);
insert into cars values (103,'mercedes',190);

               ┌──────────┐
'bmw'          │          │       owner_id|total_power
'mercedes' ───►│  query   ├───►   --------|-----------
               │          │            102|        650
               └──────────┘            101|        400
                                       103|        390
               ┌──────────┐
'bmw'          │          │       owner_id|total_power
'mercedes' ───►│  query   ├───►   --------|-----------
'audi'         │          │            101|        600
               └──────────┘            103|        490

With 2 cars, I tried this (it doesn't work):

select owner_id, sum(power) as total_power
from cars c1
inner join cars c2 on c1.owner_id = c2.owner_id
where c1.name = 'bmw' and c2.name = 'audi'
group by owner_id
order by total_power desc

I have no idea:

if there's a better way to write this for 5 cars or if 5 joins are the only way
how to make the sum of all power
how to write this in peewee

For non-peewee answers, I'm using SQLite.

Thanks

Where is the explanation? What are these results? What is 490 for author_id (or owner_id?) 103 when input is bmw, mercedes, audi? Why 102 is not in the results? — forpas, Sep 07 '22 at 13:48
@forpas I think you have all the elements, if you read carefully. You have a SQL fiddle with the data, my tentative solution, a diagram displaying input and output based on the provided data, and now even Pankaj solution. I've fixed the typo. — vault, Sep 07 '22 at 14:48
Did you check the data in the fiddle? There are these rows: `(102,'bmw',250),(102,'audi',300),(102,'mercedes',400)`. Why do you filter out 102 from the results? Also, there are these rows: `(103,'bmw',200),(103,'audi',400),(103,'mercedes',190)`. Why the result of total power for 103 would be 490 and not 790? — forpas, Sep 07 '22 at 14:55
I don't know how this fiddle works. If it's public, everybody can touch it. Last time I've saved it, 102 had no audi and `(103,'audi',400)` was `(103,'audi',100)`. — vault, Sep 07 '22 at 16:04
The link for the fiddle was edited by you in your question. Any update of the fiddle will change the url. Nobody touched your fiddle. — forpas, Sep 07 '22 at 16:09

Pankaj · Answer 1 · 2022-09-07T14:20:46.640

For SQLITE, how about something like following -

with data as 
  (select *,
  'name'||dense_rank() over (order by name) name_cnt,
  'power'||dense_rank() over (order by name) power_cnt
  from cars where name in ('bmw','audi')
 )
 select 
 owner_id, 
 max(case when name_cnt = 'name1' then name end) name1,
 max(case when power_cnt = 'power1' then power end)  power1,
 max(case when name_cnt = 'name2' then name end)  name2,
 max(case when power_cnt = 'power2' then power end)  power2,
 sum(power) total_power
 from data
group by owner_id
order by owner_id;

SQL fiddle here.

However, this does not distinct if a owner has two cars of same model/name - total though given is correct.

Updated solution to exclude owners that do not have ALL cars in the list.

with data as 
(select *,
'name'||dense_rank() over (order by name) name_cnt,
'power'||dense_rank() over (order by name) power_cnt
from cars where name in ('bmw','audi','mercedes','porsche','honda')
 ), d1 as (
 select 
 owner_id, count(distinct name) n1, count(distinct name_cnt) n2
 from data
 group by owner_id
), d2 as (
  select * from data where owner_id in
  (select owner_id from d1 
  where n1 = (select max(n2) from d1))
 ) 
select 
 owner_id, 
 max(case when name_cnt = 'name1' then name end) name1,
 max(case when power_cnt = 'power1' then power end)  power1,
 max(case when name_cnt = 'name2' then name end)  name2,
 max(case when power_cnt = 'power2' then power end)  power2,
 max(case when name_cnt = 'name3' then name end)  name3,
 max(case when power_cnt = 'power3' then power end)  power3,
 max(case when name_cnt = 'name4' then name end)  name4,
 max(case when power_cnt = 'power4' then power end)  power4,
 max(case when name_cnt = 'name5' then name end)  name5,
 max(case when power_cnt = 'power5' then power end)  power5,
 sum(power) total_power
 from d2
group by owner_id
order by owner_id;

SQL fiddle.

Thanks. Could you please update your solution for 5 cars? I think it might look bigger and more complex than 5 joins. — vault, Sep 07 '22 at 13:29
Yes, I agree it would be bigger and complex then 5 joins. The number of CTEs I've used can still be optimized (will need more time); however, still the main query itself using ```max``` makes it bigger then joins. — Pankaj, Sep 07 '22 at 13:34
The benefit of this query though is that it is dynamic - the ```IN``` list. We can give ```max``` to what-ever number of cars we want and the output will be based on the ```IN``` list. Rest of columns will show ```NULL```. — Pankaj, Sep 07 '22 at 13:44
And I don't even need `max`, I just need `owner_id` and `total_power`. I just tried your updated query with only `where name in ('mercedes')` and it returns `John 300`, that's wrong, it should be 150. — vault, Sep 07 '22 at 14:54

score 1 · Accepted Answer · answered Sep 07 '22 at 16:22

Use a CTE that returns the input car names to filter the table and use aggregation to get the total power for each owner_id.

Place the final filter in the HAVING clause:

WITH cte AS (VALUES ('bmw'), ('mercedes'))
SELECT owner_id, SUM(power) power
FROM cars
WHERE name IN cte
GROUP BY owner_id
HAVING COUNT(DISTINCT name) = (SELECT COUNT(*) FROM cte)
ORDER BY SUM(power) DESC;

See the demo.

score 0 · Answer 3 · answered Sep 08 '22 at 11:57

peewee answer:

values = ("bmw", "mercedes", "audi")
(Cars.select(Cars.owner_id, fn.SUM(Cars.power).alias("total_power"))
 .where(Cars.name.in_(values))
 .group_by(Cars.owner_id)
 .having(fn.COUNT(fn.DISTINCT(Cars.name)) == len(values))
 .order_by(SQL("total_power").desc())
 .tuples())

Dynamic self join

3 Answers3