SQL - Cheat sheet

-

99% of the SQL you write as a data analyst is not complex.


Most of it comes from a short list of simple skills.

Here is cheat sheet I assembled of must know SQL for analytics…

SELECT: The foundation of all SQL queries

- Allows you to retrieve the data
- Choose the columns you want to display in the SELECT statement
- Separate columns with a comma

WHERE: Filter your results based on criteria

- AND
- OR
- NOT
- IN (list)
- =, <, >, etc.

GROUP BY: Group rows that have the same values and aggregate data using aggregate functions

- COUNT
- SUM
- AVG
- MIN
- MAX

JOINS: Combine data from multiple tables to form a single output

- INNER JOIN
- LEFT JOIN
 
CASE WHEN: Allows you to perform conditional logic

- Example:

CASE
WHEN animal_type = 'Lion' THEN 'Carnivore'
WHEN animal_type = 'Giraffe' THEN 'Herbivore'
ELSE 'Omnivore'
END AS diet_type

WINDOW FUNCTIONS: Perform calculations across rows rather than just within a group by

- ROW_NUMBER
- RANK
- DENSE_RANK
- SUM
- ... OVER ( PARTITION BY x ORDER BY y)

DATE functions: Functions for working with dates

- EXTRACT
- DATE_ADD
- DATE_SUB
- DATE_DIFF
- CURRENT_DATE
- LAST_DAY

That's my list!

DHI₹EN

Comments

Post a Comment

Popular posts from this blog

Docker

-
Image
  What is Docker ? Docker is a containerization platform that provides easy way to containerize your applications, which means, using Docker you can build container images, run the images to create containers and also push these containers to container registries such as Docker Hub, Quay.io and so on. In simple words, you can understand as  containerization is a concept or technology  and  Docker Implements Containerization . Docker Architecture ? The above picture, clearly indicates that Docker Deamon is brain of Docker. If Docker Deamon is killed, stops working for some reasons, Docker is brain dead :p (sarcasm intended). Docker LifeCycle We can use the above Image as reference to understand the lifecycle of Docker. There are three important things, docker build -> builds docker images from Dockerfile docker run -> runs container from docker images docker push -> push the container image to public/private regestries to share the docker images. Understandin...
Read more

Data Analytics for Healthcare Industry

-
Image
Data Analytics for Healthcare Industry: Transforming Patient Outcomes The healthcare industry generates vast amounts of data every day, including patient records, medical images, and clinical trials. This data holds immense potential to improve patient outcomes, but it can also be overwhelming to analyze without the right tools and expertise. That’s where data analytics comes in - it allows healthcare providers to turn raw data into meaningful insights and drive better decision-making. Here’s a closer look at how data analytics is transforming the healthcare industry: Improving Diagnosis and Treatment Data analytics can help providers to identify trends and patterns in patient data that might not be immediately obvious. For example, data analysis can reveal correlations between certain symptoms and diagnoses, or help doctors identify which treatments are most effective for specific patients. This can lead to more accurate diagnoses and personalized treatment plans, resulting in better ...
Read more

Employees Salary Prediction Using Linear Regression - KNIME ANALYTICS PLATFORM

-
Image
Here I will be using the KNIME to forecast the employee's salaries with respect to their year of experience by training and testing data in ML using Linear Regression. Step 1 : Reading the data set with KNINE - File Reader Node 1                                         Step 2: Adding the data filter node to remove the Null Values Setting up the pattern match to N/A Step 3: Adding Node 2.1 Partitioning for Data sorting to Train & Test for Machine learning Algo. Here we set 70% of total data to Train & 30% to Test Step 4:  Training data for linear regression Note: After training the data, we have concluded that our model shows ~95% Accuracy , as we have selected the Random samples from the training data to maintain the efficiency of the model. Step 5: Adding the Linear Regression Node run Trained & Test Data Step 6: Once we have trained and tested data, we have got the ...
Read more