Finding the most active video maker within multiple tables(SQLite)

user_13861198 0
Viewed 30

How can I find the initials of the most active video maker, the one that made the most videos considering the following tables using only one query?

CREATE TABLE IF NOT EXISTS Videos (
    title TEXT PRIMARY KEY,
    video_description TEXT NOT NULL,
);

CREATE TABLE IF NOT EXISTS VideosMaker (
    full_name TEXT PRIMARY KEY NOT NULL,
    initials TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS VideosMaker_Videos (
    video_title TEXT NOT NULL,
    video_maker_name TEXT NOT NULL,

    PRIMARY KEY (video_title, video_maker_name),
    FOREIGN KEY (video_title) REFERENCES Videos(title),
    FOREIGN KEY (video_maker_name) REFERENCES VideosMaker(full_name)
);

NOTE: It is guaranteed that there will be only one video_maker who has contributed to a maximum number of news items. For this problem, foreign_keys is ON

subquery count sql-order-by sqlite sql
2 Answers

You don't care about ties, so order by and limit is good enough here.

One option is to sort with a correlated subquery that computes the video count of each maker:

select vm.initials
from videosmaker vm
order by (
    select count(*) 
    from videosmaker_videos vmv 
    where vmv.video_maker_name = vm.full_name
) desc limit 1

This query should take advantage of the index on videosmaker_videos(video_maker_name).

You can also join an aggregate subquery

select vm.initials
from videosmaker vm
inner join (
    select video_maker_name, count(*) cnt 
    from videosmaker_videos 
    group by video_maker_name
) vmv on vmv.video_maker_name = vm.full_name
order by vmv.cnt desc limit 1
user_106767160

You can get the name of the video maker who made the most videos with this query:

SELECT video_maker_name
FROM VideosMaker_Videos
GROUP BY video_maker_name
ORDER BY COUNT(*) DESC LIMIT 1

and to get the initials you must use it as a subquery in the WHERE clause:

SELECT initials
FROM VideosMaker
WHERE full_name = (
  SELECT video_maker_name
  FROM VideosMaker_Videos
  GROUP BY video_maker_name
  ORDER BY COUNT(*) DESC LIMIT 1
)
user_104988280
Related