qwertyboss

A lot of things going on top of bad health, unable to focus.

register, flashcard, Magoosh 1 - 3hrs

🤔 How would you improve engagement on Facebook?

Business objective: improve engagement for projections and trend analysis

Possible Metric / Target variable:
- Num of likes/comments/shares (l/s/c) per session
- % of users active on a daily basis
- weekly?
- monthly?

Features, Variables:
- user details
demography, age, gender, num_friends, num_liked_pages, num_groups
- user behaviour
login log, session time,
consumer_feature: type of post l/s/c, n_stories_viewed, n_stories_reacted, n_posts_interacted
creator_features: last_posted_date, l/s/c on post, last_story_date, n_views_on_story
- other factors
is_weekend, is_weekday, device_type, is_national_holiday,

Next:
- Model to use and tradeoff:
- Model output scenario/test cases:

▴ Revisit: Opencv lane detection
▴ Complete: Cassava classification article
▴ Revise: CNN, Object detection work

🤔 How would you improve engagement on Facebook?

💭 Spam mail classifier: Enron email dataset
💭 Tone detection similar to grammarly
💭 Sentiment analysis

Decent score on mock test, reducing iterations to 40.

▴ Learn the permanent way, associate the new word with familiar words. Also, It is quicker to learn new words which are somewhat related that learning totally random words.
▴ Words have a past, know the history of the word.
▴ They grow in clusters
▴ Check the prefix to guess ideas, eg: exodus "ex" means outwards in most cases.
▴ Try to know more than just the word

Unable to sit 3+ hours straight for this, only did half. Rest tomorrow.

- CASE statement
- Distinct
If you include two (or more) columns in a SELECT DISTINCT clause, your results will contain all of the unique pairs of those two columns.
- Union and Union ALL, Union will remove all the duplicates whereas UNIONALL will keep dups.
- CAST(column_name AS integer)
- Date functions:
companies.founded_at_clean::timestamp +
INTERVAL '1 week
- Window functions like PARTITION, LEAD, LAG, NTILE

Next: String functions & Date functions, Indexing, Remaining window function

- Need to take AWS/GCP/Azure & REST APIs test on Linkedin
- Read more about distributed training
- CI/CD pipelines for E2E ML Use-cases

Common words 1 done

- Book

- Flashcards -> Half way Common Words 1/7
7 parts of Basic and Advanced words' flashcard remains, a long way to go.

🏋🏻‍♀️ SQL Revision (Basics -> Intermediate)
• Comparison operation even works for letters. eg: where month_name > 'J' (This will include January). But NULL operations are not valid like greater than check for the null column.
• ILIKE is a logical operator for case insensitive matches.
• IS NULL check.
• COUNT(column_name) will give NOT NULL and NON UNIQUE values.
• COALESCE( .... ) any number of arg returns the first NOT NULL argument.
• WHERE doesn't work with aggregate columns that's where the HAVING clause comes in
• If-then equivalent is CASE- WHEN-THEN-ELSE[Optional]- END in SQL. ⭐️ It can be used inside SELECT, GROUP BY, Inside aggregate functions,
COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END)
• Declare Variables in SQL using SET @Variable1 = 'test'

🥊 Questions
• Write a query that returns all rows for songs that were on the charts in 2013 and do not contain the letter "a".
Ans: NOT ILIKE '%a%'
• How to insert a slice of a table into another table?
Ans: Insert into table (col1, col2 ...) values (select col1, col2... )
• Write a query that counts the number of 300lb+ players for each of the following regions: West Coast (CA, OR, WA), Texas, and Other (everywhere else).
Ans: group by case ... end

Next: https://mode.com/sql-tutorial/sql-case/