Thought experiment on SQL queries optimization

I am relatively new in SQL and I need some help/ideas to optimize some queries.

Unfortunately, I can’t share the data because it is confidential but you will see that it's a typical case of triangulating 3 tables (one with the scope of analysis, other with transactions and the third one with transaction meta data).

Consider it a thought experiment, like the one of Einstein and the mirror travelling at the speed of light when he discover special relativity:), here share the most important pieces of information. There are 3 tables:

1. Reading : Key: Reading_ID and Store_ID. The table has around 1 Billion records, the most important columns are Reading_ID, Store ID and Reading_Date. Each line is logging the reading_id x store_id x reading_date, and as can be expected each reading_id is linked to only 1 store_id

   Basically, this table will help us retrieve the last reading per store  

2. Books: Key: Reading_ID and Book_Type with a total of 10 Billion records, logging the incremental (cumulative) number of books sold by Book_Type (and indirectly per store, as each reading is performed in a given store). There are 32 Book_Types (INT from 1 to 40), for Reading_ID we know how many books we have sold so far (cumulative) for each Book_Type

Basically, knowing the last reading_id per store from previous table will allow us to check what is the last known number of book solds by Book Type.

3. Stores: Key: Store_id + descriptive attributes (which are not important), around 500k records, which define the scope/perimeter of analysis of stores.

  Goal: for all the stores present in the table Stores,

• Take the last transaction (i.e. taking the most recent date per store in table “Transaction”. Alternatively, taking the Max of transaction_id, assuming a clean and incremental relationship between transaction_id and transaction_date)
• Calculate the last known total cumulated number of books sold per store and the proportion by book type with respect to that total
• Calculate the most important book type and aggregate also proportion calculated in previous step by different group of book types (each book type is linked to a given group of book type).
• With previous result (proportion by group of book type + most important book type), classify the store (basically, it’s a label deduced from the statistics calculated before).

Problem: I know how to calculate what it is asked either by using nested selects or going with window functions, but I feel I can do better and find a more efficient way to do it, as solving this issue will help me optimize (very) similar queries (instead of “Books” it would be applied to “Magazines”). I have tried many things: do all intermediate steps by CTE and a final select, do all intermediate steps with #temp tables and a final select, a mixed of both, using window functions to decrease the number of steps, but I didn’t try anything more complex (adding cluster/non cluster index for ex, and other things that I don’t know).

So far, the best approach (to my surprise) is the “brute” approach: using #temp tables all the way (4 in total), “simple” code without any window function. In order of efficiency:

• 4 temp tables + final select : 16 minutes
• CTE1 + CTE2 with window function + CTE3 + final select : 30 minutes
• CTE 1+ temp2 with window function + CTE2 + final select: 29 minutes
• CTE1+ temp1 with window function + temp2 + final select: 25 minutes

Note: the trend points in the direction of using only temp tables instead of CTEs, and I know from other post in StackOverflow that CTEs maybe more elegant but are generally worse performing (this has been my experience so far), even though I only use read/inquire them once each of them in my code.

I need help with:

a) Considering the nature of the problem and the number of records in each: is it a better way to approach it, do you have any more ideas that I can try, why and where?? Ex: using a clustered/non clustered index somewhere

b) Generally speaking and independently of this problem, any useful article/book/video that can help me build/follow the best practices when writing SQL queries? I thought that doing CTEs at the beginning to restrain the scope of each tables, making sure to inquire/call them only once, before merging them would help but it hasn’t been the case here.

Thank you for you enlightening :)