- Rohinton Mistry

I wanted diwali vibes indian stories, thought not quite i wanted, but the mood, the nostalgia and the emotion was all I needed. The book had put me in questions that no one ever can. The perspective of a family is not confided in four walls but four relations

- Toshikazu Kawaguchi

The sweet tales of time travelling cafe are so wonderful and almost gives a cope(escapism) from the world yet puts you on the path to still walk for your people. Such a beautiful blend of reality and fiction. I can't believe I read 5 books in a series

Some authors have stood out to me these year that I would be forever grateful for
- Ami Ganatra
Ramayana, Mahabharata, she bought facts, relatable views and modern day links to the tale, all 3 books are like gateway drug to indian mythology. I now need to read the actual legends

Really a huge shoutout to @aaronfrancis.com for teaching it enthusiastically, its contagious! Can't wait to add more to my sqlog series, diving deeper into SQLite.

The lore in the problems was epic! I am inspired to create something like that!

And many naunced things that I think are quite lame to share it.
But anyways, I loved these problems, the soptify wrapped like artists problem was sooooo good.
I actually felt I understood and solved something really cool, something real!
The missing dates, so many cool problems!

8. Calculating rolling averages with sliding window using window functions
9. Full text search in SQLite
10. Counting the number of occurrences of a word in a string
11. Recursive CTEs to traverse a network like path

4. Window Functions (like groups but more customizable)
5. Generating series of dates in SQLite using recursive CTE
6. Using json functions to extract list and key-value pairs from json string
7. CTEs in SQLite cannot have DELETE or INSERT queries (sigh! ruined day 10 solution)

What all did I learn?

1. JOINs (the crucial difference in lift, right and inner join)
2.CTE and Subquery (those are the same things, CTEs help you to reuse and name it as sub-query)
3. UNION and UNION ALL (why even have union all hhh?)

I completed a advent challenge for the first time (it was just 15 days, I feel happy and sad about that)

But I am mostly happy and content with the things I learnt and got to do in SQL.

I know can confidently say, I know the bare minimum SQL!

Day 32:
Many to Many Relation Table (Junction Table) without Row id
bsky.app/profile/meet...

Read more here for interactive SQL codeblocks and playground like environment:
www.meetgor.com/sqlog/sqlite...

Only in the case of author_post or junction table, where the primary key is used as a combination of other two foreign keys, we don't need the rowid as the junction table doesn't have any unique data in it, it is just the mapping of two existing data records in the other two tables in the database

Both the tables users and posts will have rowid referenced or aliased as id of their respective columns as we know because of the special case of INTEGER PRIMARY KEY
SELECT rowid, _rowid_, * FROM users;

SELECT rowid, _rowid_, * FROM posts;

This would return error as the rowid doesn't exists.
We will still need the rowid in posts and users table as those are the PRIMARY KEY columns.

The rest of the commands from inserts to selection and all of the things we saw above will remain the same.

However, when you query the author_post table, the rowid will not be returned as it doesn't exists.

SELECT * FROM author_post;
SELECT rowid, * FROM author_post;

CREATE TABLE author_post (user_id INTEGER NOT NULL,post_id INTEGER NOT NULL, PRIMARY KEY (user_id, post_id), FOREIGN KEY (user_id) REFERENCES users (id),FOREIGN KEY (post_id) REFERENCES posts (id) )
WITHOUT ROWID;