Day 6 of 30 days of Data Engineering Series with Projects

Welcome back peeps to Day 6 of Data Engineering Series with Projects!

Day 1 : What’s Data Engineering, Why Data Engineering, Data Engineers — ML Engineers — Data Scientists, Purpose and Scope

Day 2 : Complete Python for Data Engineering — Part 1

Day 3 : Complete Advanced Python for Data Engineering — Part 2

Day 4: Techniques to write efficient and Optimized Code

Day 5 : SQL

Day 6 : Advanced SQL

Projects Videos —

All the projects, data structures, SQL, algorithms, system design, Data Science and ML , Data Analytics, Data Engineering, , Implemented Data Science and ML projects, Implemented Data Engineering Projects, Implemented Deep Learning Projects, Implemented Machine Learning Ops Projects, Implemented Time Series Analysis and Forecasting Projects, Implemented Applied Machine Learning Projects, Implemented Tensorflow and Keras Projects, Implemented PyTorch Projects, Implemented Scikit Learn Projects, Implemented Big Data Projects, Implemented Cloud Machine Learning Projects, Implemented Neural Networks Projects, Implemented OpenCV Projects,Complete ML Research Papers Summarized, Implemented Data Analytics projects, Implemented Data Visualization Projects, Implemented Data Mining Projects, Implemented Natural Leaning Processing Projects, MLOps and Deep Learning, Applied Machine Learning with Projects Series, PyTorch with Projects Series, Tensorflow and Keras with Projects Series, Scikit Learn Series with Projects, Time Series Analysis and Forecasting with Projects Series, ML System Design Case Studies Series videos will be published on our youtube channel ( just launched).

Subscribe today!

Tech Newsletter —

If you are interested, you can join my newsletter through which I send tech interview tips, techniques, patterns, hacks — Software Development, ML, Data Science, Startups and Technology projects to more than 30K readers. You can subscribe to Ignito:

System Design Case Studies — In Depth

Design Instagram

Design Netflix

Design Reddit

Design Amazon

Design Messenger App

Design Twitter

Design URL Shortener

Design Dropbox

Design Youtube

Design API Rate Limiter

Design Web Crawler

Design Amazon Prime Video

Design Facebook’s Newsfeed

Design Yelp

Design Uber

Design Tinder

Design Tiktok

Design Whatsapp

Most Popular System Design Questions

Mega Compilation : Solved System Design Case studies

Pre-requisite to Day 6 is to complete Day 1–5( link below):

Day 1 of 30 days of Data Engineering can be found below —

Day 2 of 30 days of Data Engineering can be found below —

Day 3 of 30 days of Data Engineering can be found below —

This is Day 6 of 30 days of Data Engineering Series where we will be covering —

Advanced SQL

Let’s get started!

Some of the most important things in advanced SQL include:

1. Subqueries: These are queries that are nested within another query and are used to retrieve data from multiple tables at once.
2. Joins: These are used to combine data from multiple tables based on a common column, allowing for more complex data retrieval and analysis.
3. Views: These are virtual tables that can be created to simplify complex queries and make it easier to access specific data from a large database.
4. Stored Procedures: These are pre-compiled, reusable code blocks that can be used to perform complex or repetitive tasks on a database, improving performance and ease of use.
5. Transactions: These are used to ensure that a series of SQL statements are executed as a single unit of work, either all of them or none, thus ensuring data consistency and integrity.
6. Indexing: This is a technique that can be used to speed up the performance of database searches by creating a separate data structure that maps the values in certain columns to their corresponding rows in the table.
7. Advanced data manipulation and analysis: These include using SQL to perform complex data analysis, such as aggregating data, creating pivot tables, and performing statistical calculations.

Code Implementation —

-- Subqueries
-- Example: Retrieve the names of customers who have placed orders
SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Joins
-- Example: Retrieve the orders along with the customer names
SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

-- Views
-- Example: Create a view to simplify querying customer orders
CREATE VIEW vw_customer_orders AS
SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

-- Stored Procedures
-- Example: Create a stored procedure to calculate order totals
CREATE PROCEDURE calculate_order_total(order_id INT)
AS
BEGIN
  SELECT SUM(quantity * price) AS order_total
  FROM order_items
  WHERE order_id = @order_id;
END;

-- Transactions
-- Example: Perform a transaction to insert an order and its items
BEGIN TRANSACTION;
  
  INSERT INTO orders (order_id, customer_id) VALUES (1, 123);
  
  INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 456, 2);
  INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 789, 3);
  
COMMIT;

-- Indexing
-- Example: Create an index on the customer_name column for faster searches
CREATE INDEX idx_customer_name ON customers (customer_name);

-- Advanced data manipulation and analysis
-- Example: Calculate the average order total per customer
SELECT customers.customer_id, customers.customer_name, AVG(order_totals.order_total) AS avg_order_total
FROM customers
JOIN (SELECT order_id, SUM(quantity * price) AS order_total
      FROM order_items
      GROUP BY order_id) AS order_totals
ON customers.customer_id = order_totals.customer_id
GROUP BY customers.customer_id, customers.customer_name;

Subqueries in SQL

These are nested queries statements which is used when we need results from multiple tables using multiple filters.

These subqueries consists of SQL statements like UPDATE, SELECT, DELETE, ALTER, INSERT.

There are 4 types of Subqueries -

1. Single row Subquery
2. Multiple Row Subquery
3. Nested Subqueries
4. Correlated Subqueries

Format —

SELECT t1, t2

FROM table1

WHERE VALUE IN

(SELECT r1

FROM table2

WHERE condition)

The subquery executes from the inner query to the outer query. They should be enclosed in the parentheses.

The benefits of using subqueries —

1. Provides better readability
2. Used to extract/work on complex analytics
3. Easy and can be maintained for different databases

Code Implementation —

-- Single row Subquery
SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM another_table WHERE condition);

-- Multiple Row Subquery
SELECT column_name
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

-- Nested Subqueries
SELECT column_name
FROM table_name
WHERE column_name IN (SELECT column_name
                      FROM another_table
                      WHERE column_name IN (SELECT column_name FROM yet_another_table WHERE condition));

-- Correlated Subqueries
SELECT column_name
FROM table_name t1
WHERE column_name = (SELECT column_name FROM another_table t2 WHERE t2.id = t1.id);

Group By, Order By and HAVING clauses in SQL

The aggregate functions are often used in conjunction with DISTINCT, GROUP BY and HAVING clauses. GROUP BY clause can group by one or more columns from your table. It is often used with aggregate functions where it groups your data records into summary rows.

Format —

SELECT column_names

FROM table

WHERE condition

GROUP BY column_names

GROUP BY with aggregate function format —

SELECT COUNT(column_name)

FROM table

GROUP BY column_name

HAVING clause is used to summarize group records returned by GROUP BY. It’s mandatory to use GROUP BY whenever you are using a HAVING clause.

Format —

SELECT column_names

FROM table

WHERE condition

GROUP BY column_names

HAVING condition

Order by is used to return the records in specific sort order i.e Ascending or Descending.

Format —

SELECT column_names

FROM table

WHERE condition

ORDER BY column_names

Example —

/* Order By */

SELECT column_names 
FROM table 
WHERE condition
GROUP BY column_name
ORDER BY column_name ASC|DESC
LIMIT

/* Group By */

SELECT COUNT(column_name)
FROM table
GROUP BY column_name

/* Having Clause */

SELECT column_names
FROM table
WHERE condition
GROUP BY column_names
HAVING condition

Analytical Functions

Analytical Function computes results over a group of rows. All analytic functions have an OVER clause, which defines the sets of rows used in each calculation which has three (optional) parts:

• The PARTITION BY clause divides the rows of the table into different groups.
• The ORDER BY clause defines an ordering within each partition.
• The final clause (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) is known as a window frame clause. It identifies the set of rows used in each calculation.

The window frame clauses can have various functions like —

• ROWS BETWEEN 1 PRECEDING AND CURRENT ROW — the previous row and the current row.
• ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING — the 3 previous rows, the current row, and the following row
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — all rows in the partition.

The analytical numbering functions which we can use with analytical functions are -

• ROW_NUMBER() — Returns the order in which rows appear in the input (starting with 1)
• RANK() — All rows with the same value in the ordering column receive the same rank value, where the next row receives a rank value which increments by the number of rows with the previous rank value.

Format 1

SELECT column_names,

SUM(column_name)

OVER ( PARTITION BY column_name

ORDER BY column_name

ROWS UNBOUNDED PRECEDING

) AS Alias_name

FROM Table

Format 2

SELECT column_names

RANK() OVER (PARTITION BY column_name ORDER BY column_name) AS rank

FROM Table

Code Implementation —

SELECT 
    column_name,
    ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column) AS row_number,
    RANK() OVER (PARTITION BY partition_column ORDER BY order_column) AS rank_number
FROM 
    table_name

Window Functions in SQL

In the window functions, the input values are taken from a specified window which consists of one or more rows in the results returned by the SELECT.

There are four types of window functions in SQL —

1. Ranking Functions
2. Distribution Functions
3. Analytical Functions
4. Aggregate Functions

For Ranking Functions —

row_number() : gives unique no of each row within the partition

rank(): gives ranks within the partitions ( with gaps)

dense_rank(): gives ranks within the partitions ( without gaps)

For Distribution Functions —

percent_rank(): gives the percentile ranking no of a row

cume_dist() : gives the cumulative distribution of the values within a group of values

For Analytical Functions —

ntile() : it divides the rows within a partition into n groups

nth_value(): gives the nth value of the nth row in a given window frame

last_value(): gives the value of the last row in a given window frame

first_value(): gives the value of the first row in a given window frame

For Aggregate Functions —

It’s used to perform calculation on mutiple rows/values and return a single result as the answer. It ignores NULL values when performing aggregation.

Some of the most important aggregate functions are —

• COUNT() — To count the number of rows
• SUM() — To return sum of the column
• AVG () — To return average of the column
• MIN() — To return min value in the column
• MAX() — To return max value in the column

Format —

Count(column_name)

Sum(column_name)

Avg(column_name)

Min(column_name)

Max(column_name)

Example —

SELECT COUNT(employee_id)

FROM employee_table

WHERE salary > 40000

SELECT MAX(salary)

FROM employee_table

SELECT MIN(salary)

FROM employee_table

SELECT AVG(salary)

FROM employee_table

SELECT SUM(salary)

FROM employee_table

SELECT
    column_name,
    row_number() OVER (PARTITION BY partition_column ORDER BY order_column) AS row_number,
    rank() OVER (PARTITION BY partition_column ORDER BY order_column) AS rank,
    dense_rank() OVER (PARTITION BY partition_column ORDER BY order_column) AS dense_rank,
    percent_rank() OVER (PARTITION BY partition_column ORDER BY order_column) AS percent_rank,
    cume_dist() OVER (PARTITION BY partition_column ORDER BY order_column) AS cume_dist,
    ntile(4) OVER (PARTITION BY partition_column ORDER BY order_column) AS ntile,
    nth_value(column_name, 3) OVER (PARTITION BY partition_column ORDER BY order_column) AS nth_value,
    last_value(column_name) OVER (PARTITION BY partition_column ORDER BY order_column) AS last_value,
    first_value(column_name) OVER (PARTITION BY partition_column ORDER BY order_column) AS first_value,
    COUNT(column_name) OVER () AS count_all,
    SUM(column_name) OVER () AS sum_all,
    AVG(column_name) OVER () AS avg_all,
    MIN(column_name) OVER () AS min_all,
    MAX(column_name) OVER () AS max_all
FROM
    table_name;

Grouping Sets in SQL

A grouping set us a group of columns by which you create a group. For example a single SQL statement combined with Aggregate functions is a single grouping set.

It is an extension of the group by clause which is used to summarize the data.

Format —

SELECT column_list, aggregate(t1)

FROM table_name

GROUP BY

GROUPING SETS (

(t1, t2),

(t1),

(t2))

Grouping sets takes those set of columns which are to be grouped together.

Example —

SELECT employee_name, City, Year, SUM(Salary) AS TotalSalary

FROM employee_table

GROUP BY GROUPING SETS ((City), (Year), (City, Year),())

ORDER BY City, Year

SELECT
    employee_name,
    City,
    Year,
    SUM(Salary) AS TotalSalary
FROM
    employee_table
GROUP BY
    GROUPING SETS (
        (City),
        (Year),
        (City, Year),
        ()
    )
ORDER BY
    City, Year;

Constraints in SQL

In order to avoid ambiguity in the data, we use Constraints which helps us better manage our data. These are nothing but rules which help us maintain integrity of the data and are applied at two levels —

1. Table level — applied to the whole table
2. Column level — applied to the data which is stored in the columns

Format —

CREATE TABLE table_name

(

column1 datatype constraint1,

column2 datatype constraint2,

….

)

Example —

CREATE TABLE employee

(

emp_Id int(10) NOT NULL UNIQUE,

employee_name varchar(10),

email varchar(20)

)

In the above example we are using NOT NULL and UNIQUE constraint.

CREATE TABLE employee
(
    emp_Id int(10) NOT NULL UNIQUE,
    employee_name varchar(10),
    email varchar(20),
    CONSTRAINT pk_employee PRIMARY KEY (emp_Id),
    CONSTRAINT ck_email CHECK (email LIKE '%@%')
);

That’s it for now. Day 7 Coming soon!

Let me know if you have questions in the comment section below. Subscribe/ Follow, Like/Clap as it would encourage me to write more in my free time

Stay Tuned!!

Read more —

All the Complete System Design Series Parts —

1. System design basics

2. Horizontal and vertical scaling

3. Load balancing and Message queues

4. High level design and low level design, Consistent Hashing, Monolithic and Microservices architecture

5. Caching, Indexing, Proxies

6. Networking, How Browsers work, Content Network Delivery ( CDN)

7. Database Sharding, CAP Theorem, Database schema Design

8. Concurrency, API, Components + OOP + Abstraction

9. Estimation and Planning, Performance

10. Map Reduce, Patterns and Microservices

11. SQL vs NoSQL and Cloud

12. Most Popular System Design Questions

Github —

Keep learning and coding ;)

Day 5 coming soon!

For Python Projects —

For complete 60 days of Data Science and ML : Day 1 — Day 60 : Quick Recap of 60 days of Data Science and ML

Follow for more updates. Stay tuned and keep coding! Disclosure: Some of the links are affiliates.

For other projects, tune to —

Build Machine Learning Pipelines( With Code)

Recurrent Neural Network with Keras

Clustering Geolocation Data in Python using DBSCAN and K-Means

Facial Expression Recognition using Keras

Hyperparameter Tuning with Keras Tuner

Custom Layers in Keras