SQL Join

 cross join – match combinations

union – stack, unique

union all – stack, include duplicates

intersect – result in the intersection

except – result in the exception

semi join – select rows from 1st table that are ALSO present in 2nd table
where (field) in (table)

anti join – select rows from 1st table that are NOT present in 2nd table
where (field) not in (table)

2018-11-06 13_41_15-Semi-joins and Anti-joins _ Sql.png

 

Subclause can be used instead of group by!

 

Comments

Post a Comment

Popular posts from this blog

Jonas - Javascript

  variables – var abc = ”, – data types   Section 3 – How JS works Scoping When refer to a var that is not available in the current scope, the program will look to the outer scope (or even outer outer scope)   Section 4 – DOM Manipulation   classList, .add .remove classes .toggle, turn on/off the “active” class   Section 5 – Objects and Functions Everything is an object, check with  console.log () for __proto__ when we try to access properties or methods of an object it will look in its own scope first, then its proto parents, and so on. call, apply, bind, to change the  this  variable. And to pre-set some arguments of a function  
Read more

R - Supervised Learning

Image
  knn k nearest neighbors, k library(class) knn(training data, test data, labels), extra args: k = 2. prob = TRUE enables probability of each guess. Primitive bayes nrows(df), count rows subset(df, conditions/ col > 0 etc.), nrows this subset then do divisions Naive bayes model <- naive_bayes(y ~ x1 + x2 + … , data = df) y as explained by x1, x2 etc. predict(model , test_df) + args type = “prob” show posterior probability (instead of priori overall prob) Laplace correction:  add 1 to prevent the 0% probability affects all estimation Logistic Regression model <- glm(y ~ x1 + x2 …, data = df, family = “binomial”) predict(model, test_df, type = “response”) ROC curve to measure how well the model performs, AUC area under curve value from 0 – 1, 1 is best. library(pROC) ROC <- roc(actual var, predicted var) plot(ROC), auc(ROC) Decision Tree library(rpart) model <- rpart(y ~ x1 + x2 + …, data = df, method = “class”, control = rpart.control(cp = 0)) predict(m...
Read more

Consulting Interview

  Numerical skills – rather simple tips on quick estimation, rounding Top down approach is preferred Hypothesis – Always start with a hypothesis, then reason with the info to either prove it or disprove it. Don’t present info and a bunch of rationales before the conclusion, it’s just less clear to the listeners, especially C-level executives. Synthesis – Let’s do A. Because of B, C and D.
Read more