Select random records until a cumulative total is exceeded
0
votes
2
answers
83
views
I have a table of videos with an
id
, skill_level
and duration
column and I am attempting to write a query that will return a playlist of distinct videos that has a total duration exceeding X
.
select _id, duration, running_total
from (select *, sum(duration) over (order by RANDOM()) as running_total
from videos
where skill_level in ('beginner', 'superbeginner') AND watched IS NULL
)
where running_total <= 7200;
This works and returns the following:
5e4d11fbaac87f3820954c45|305|305
64e51ad279223a9ad03525c2|930|1235
5e4d1204aac87f3820954c64|627|1862
5e4d1212aac87f3820954c95|367|2229
641e05c2d7c1512d278dc51a|361|2590
5e4d1200aac87f3820954c57|444|3034
649aa7089776ba12d8de28d5|329|3363
5e4d123eaac87f3820954d2c|573|3936
5e4d1216aac87f3820954ca1|399|4335
5e4d120daac87f3820954c84|315|4650
5e4d11dfaac87f3820954be1|510|5160
64f7036d3d578af923ba2afc|326|5486
5e4d11fcaac87f3820954c47|437|5923
5fa51afc27a912152cc51c33|1062|6985
But ideally I want to return enough records to exceed X
but only by one. 6375274c1cff4a7e31b04a0d
below puts us over 7200
.
5e4d11fbaac87f3820954c45|305|305
64e51ad279223a9ad03525c2|930|1235
5e4d1204aac87f3820954c64|627|1862
5e4d1212aac87f3820954c95|367|2229
641e05c2d7c1512d278dc51a|361|2590
5e4d1200aac87f3820954c57|444|3034
649aa7089776ba12d8de28d5|329|3363
5e4d123eaac87f3820954d2c|573|3936
5e4d1216aac87f3820954ca1|399|4335
5e4d120daac87f3820954c84|315|4650
5e4d11dfaac87f3820954be1|510|5160
64f7036d3d578af923ba2afc|326|5486
5e4d11fcaac87f3820954c47|437|5923
5fa51afc27a912152cc51c33|1062|6985
6375274c1cff4a7e31b04a0d|1138|8123
Is it possible to write a query to achieve this in sqlite?
Asked by Ashley
(103 rep)
Feb 21, 2024, 03:52 AM
Last activity: Feb 27, 2024, 04:04 PM
Last activity: Feb 27, 2024, 04:04 PM