Well that's interesting. I checked around a bit and tried a few things. Seems that SQLite doesn't support LATERAL joins, but you can get the same effect by putting the subquery in the JOIN condition[0]. I'm not sure if they're exactly the same and you can do all of the same things, but it worked for the quick use-case I came up with:

    select a.username, p2.*
    from accounts a
    join posts p2 on p2.id in (select p1.id from posts p1 where p1.account_id = a.id order by p1.score desc limit 2);

[0] https://dba.stackexchange.com/a/100850/