SQL Select newest (public) post of each user

Question

An example of the data could look like:

username	tweet	is_public	created_at
catelie	tweet1	1	2021-06-14
urs	tweet1	1	2021-06-15
nik	firstlast	1	2021-06-15
urs	lasttweet	1	2021-06-23
nik	empty	0	2021-06-23

To select all public tweets:

 WHERE is_public = 1 

To select newest tweet per user I was inspired by these so I got:

SELECT posts.* FROM post
  LEFT JOIN posts as postdouble
    ON posts.created_at < postdouble.created_at AND posts.username = postdouble.username
WHERE postdouble.username IS NULL

To select newest and public tweet per user (mix up these two statements) I'm lost by some SubSubQueries

SELECT posts.username, posts.tweet, posts.is_public, posts.created_at, postdouble.username as userd, postdouble.tweet as tweetd, postdouble.is_public as ispublicd, postdouble.created_at as createdatd FROM posts
  LEFT JOIN posts as postdouble
    ON posts.created_at < (
         SELECT MAX(posts.created_at) FROM posts WHERE is_public = 1
      )
    AND posts.username = postdouble.username
-- WHERE postdouble.username IS NULL

urs is selected right (empty entities in doubles) but that's the only thing thats right. Please help me with some debugging.thx^^lg

Answer 1

You are overcomplicating this. You can use a correlated subquery, but you don't need an additional JOIN:

SELECT p.*
FROM posts p
WHERE p.created_at = (SELECT MAX(p2.created_at)
                      FROM posts p2
                      WHERE p2.is_public = 1 AND
                            p2.username = p.username
                     ) AND
      p.is_public = 1;

The correlation clause makes sure it is for the same user.

For optimal performance, you want an index on posts(username, public, created_at).