Showing posts with label learning. Show all posts
Showing posts with label learning. Show all posts

Sunday

Basic SQL Knowledge Test For Beginner

photo: pexel

Are you new to SQL and preparing for your first job that requires SQL knowledge? 

This blog post is designed specifically for beginners like you who are just starting out on their journey with SQL. Whether you're a student, a recent graduate, or someone looking to transition into a tech role, this guide will help you build a strong foundation in SQL concepts, understand common SQL queries, and prepare you for the types of SQL tasks you might encounter in your first job. We'll break down complex concepts into easy-to-understand steps, provide practical examples, and give you tips to succeed in interviews and on the job. Let's get you job-ready with SQL!

Below is the table structure for the `customer`, `user_account`, and `contact` tables in a more visual format:






Below are the SQL statements to create the `customer`, `user_account`, and `contact` tables according to the schema provided:

1. `customer` Table
```sql
CREATE TABLE customer (
    id INT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    city_id INT,
    customer_address VARCHAR(255),
    contact_person VARCHAR(255),
    email VARCHAR(128),
    phone VARCHAR(128),
    is_active INT
);
```

2. `user_account` Table
```sql
CREATE TABLE user_account (
    id INT PRIMARY KEY,
    first_name VARCHAR(64) NOT NULL,
    last_name VARCHAR(64) NOT NULL,
    user_name VARCHAR(128) NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(128),
    phone VARCHAR(128),
    is_active INT
);
```

3. `contact` Table
```sql
CREATE TABLE contact (
    id INT PRIMARY KEY,
    user_account_id INT,
    customer_id INT,
    contact_type_id INT,
    contact_outcome_id INT,
    additional_comment VARCHAR(255),
    initiated_by_customer INT,
    initiated_by_user INT,
    FOREIGN KEY (user_account_id) REFERENCES user_account(id),
    FOREIGN KEY (customer_id) REFERENCES customer(id)
);
```


Here’s the SQL query to find all pairs of customers and agents (users) who have been in contact more than once. For each pair, the query displays the user ID, first name, last name, customer ID, customer name, and the number of their contacts. The result is ordered by the user ID in ascending order:

```sql
SELECT 
    ua.id AS user_id,
    ua.first_name,
    ua.last_name,
    c.id AS customer_id,
    c.customer_name,
    COUNT(ct.id) AS contact_count
FROM 
    contact ct
JOIN 
    user_account ua ON ct.user_account_id = ua.id
JOIN 
    customer c ON ct.customer_id = c.id
GROUP BY 
    ua.id, ua.first_name, ua.last_name, c.id, c.customer_name
HAVING 
    COUNT(ct.id) > 1
ORDER BY 
    ua.id ASC;
```

Explanation:

- JOIN: The `contact` table is joined with `user_account` and `customer` tables based on the `user_account_id` and `customer_id` respectively.

- GROUP BY: Groups the results by `user_id`, `first_name`, `last_name`, `customer_id`, and `customer_name` to aggregate the contacts.

- HAVING COUNT(ct.id) > 1: Filters out the pairs that have been in contact more than once.

- ORDER BY ua.id ASC: Orders the result by `user_id` in ascending order.


Here are two slightly more complex SQL query questions along with their detailed explanations:


Question 1: Find the Top 3 Customers by Total Purchase Amount


You have a `customer` table and an `invoice` table. The `invoice` table contains information about customer purchases, including the `customer_id`, `total_amount`, and `invoice_date`. Your task is to find the top 3 customers who have spent the most, along with their total amount spent and the number of purchases they made. Order the result by total amount spent in descending order.


Tables:

- customer: Contains customer information.

  - `id`: INT (Primary Key)

  - `customer_name`: VARCHAR(255)

- invoice: Contains invoice information.

  - `customer_id`: INT (Foreign Key referencing `customer.id`)

  - `total_amount`: DECIMAL(10,2)

  - `invoice_date`: DATE


Query:

```sql

SELECT 

    c.id AS customer_id,

    c.customer_name,

    SUM(i.total_amount) AS total_spent,

    COUNT(i.id) AS total_purchases

FROM 

    customer c

JOIN 

    invoice i ON c.id = i.customer_id

GROUP BY 

    c.id, c.customer_name

ORDER BY 

    total_spent DESC

LIMIT 3;

```


Explanation:

- JOIN: Combines the `customer` and `invoice` tables based on the `customer_id`.

- SUM(i.total_amount): Calculates the total amount each customer has spent.

- COUNT(i.id): Counts the number of invoices (purchases) for each customer.

- GROUP BY c.id, c.customer_name: Groups the results by customer.

- ORDER BY total_spent DESC: Orders the results by the total amount spent in descending order.

- LIMIT 3: Limits the result to the top 3 customers.


---


Question 2: Find All Users Who Have Never Made a Purchase


You have a `user_account` table and an `order` table. Some users might have registered but never made a purchase. Your task is to find all users who have never placed an order, displaying their user ID, first name, last name, and email.


Tables:

- user_account: Contains user information.

  - `id`: INT (Primary Key)

  - `first_name`: VARCHAR(64)

  - `last_name`: VARCHAR(64)

  - `email`: VARCHAR(128)

- order: Contains order information.

  - `user_id`: INT (Foreign Key referencing `user_account.id`)

  - `order_date`: DATE


Query:

```sql

SELECT 

    ua.id AS user_id,

    ua.first_name,

    ua.last_name,

    ua.email

FROM 

    user_account ua

LEFT JOIN 

    order o ON ua.id = o.user_id

WHERE 

    o.user_id IS NULL;

```


Explanation:

- LEFT JOIN: Joins the `user_account` table with the `order` table to include all users, even if they haven't placed an order.

- WHERE o.user_id IS NULL: Filters out only those users who have never made an order (i.e., no matching record in the `order` table).

- ua.id, ua.first_name, ua.last_name, ua.email: Selects the relevant user information for the result.


You can practice SQL without installing a database server by using online SQL platforms like:


1. SQLFiddle (www.sqlfiddle.com): Allows you to write and execute SQL queries in an interactive online environment.

2. DB Fiddle (www.db-fiddle.com): Similar to SQLFiddle, supporting multiple database systems for practice.

3. LeetCode (www.leetcode.com): Offers SQL challenges to solve directly in your browser.

4. HackerRank (www.hackerrank.com/domains/sql): Provides SQL problems with an in-browser SQL editor for practice.

5. Mode Analytics SQL Tutorial (www.mode.com/sql-tutorial/): Offers an interactive SQL tutorial where you can write and test queries.


These tools allow you to practice SQL queries without needing to install anything on your local machine.


Twelve Weeks Plan to Learn AI

 

12-Week + Project Study Plan for AI/ML:

Module 1: Introduction to the AI Landscape (1 Week)

  • Topics: Overview of AI, Data Analysis, Prediction, Decision Making, Causal Inference
  • Activities: Lectures, readings, self-study

Module 2: Data Exploration - Structured Data (2 Weeks)

  • Topics: Data Exploration Techniques, Dimensionality Reduction (PCA), Clustering (K-means, DBSCAN)
  • Activities: Hands-on exercises, data analysis projects, self-study

Module 3: Prediction Methods - Regression (2 Weeks)

  • Topics: Linear Regression, Model Evaluation, Regularization, Overfitting
  • Activities: Hands-on exercises, building regression models, self-study

Module 4: Decision Systems (2 Weeks)

  • Topics: Decision Trees, Classification, Ensemble Learning (Random Forests)
  • Activities: Hands-on exercises, building decision trees and random forests, self-study

Module 5: Data Exploration - Unstructured Data (2 Weeks)

  • Topics: Natural Language Processing (NLP), Sentiment Analysis
  • Activities: Hands-on exercises, analyzing text data, building sentiment analysis models, self-study

Module 6: Recommendation Systems (1 Week)

  • Topics: Collaborative Filtering, Recommendation System Techniques
  • Activities: Guest lecture, case studies, self-study

Module 7: Data Exploration - Temporal Data (1 Week)

  • Topics: Time Series Analysis, Stationarity, Time Series Forecasting
  • Activities: Guest lecture, case studies, self-study

Module 8: Prediction Methods - Neural Networks (2 Weeks)

  • Topics: Neural Networks Architecture, Forward Propagation, Backpropagation, Optimization
  • Activities: Hands-on exercises, building and training neural networks, self-study

Module 9: Computer Vision Methods (2 Weeks)

  • Topics: Convolutional Neural Networks (CNNs), Image Processing, Feature Extraction
  • Activities: Hands-on exercises, building and training CNNs, self-study

Module 10: Workflows and Deployment (1 Week)

  • Topics: Industry Case Studies, Practical Applications of AI/ML
  • Activities: Guest lectures, group discussions, self-study

Projects:

  • Insurance Claim Prediction (2 Weeks)
    • Tools & Concepts: Linear Regression, Model evaluation, Tuning, Exploratory Data Analysis, Python
  • Forecasting Monthly Sales of French Champagne (2 Weeks)
    • Tools & Concepts: Time Series Analysis, Predictive Modelling, Python for Time Series

Self-study:

  • Allocate time each week for self-study, reviewing concepts, and completing additional exercises.
  • Utilize online resources, tutorials, and books to supplement your learning.

Additional Notes:

  • Consider your learning style and adjust the pace of the study plan as needed.
  • Join online communities and forums to connect with other AI/ML enthusiasts.
  • Participate in hackathons and challenges to apply your knowledge and gain experience.
  • Continuously update your skills and stay informed about the latest advancements in AI/ML.

I have created more details below.

Module 1: Introduction to the AI Landscape

To offer a general overview of the four blocks upon which this No Code AI and Machine Learning Program is focused.


Understanding the data: What is it telling us?

Prediction: What is going to happen?

Decision Making: What should we do?

Causal Inference: Did it work?


Module 2: Data Exploration - Structured Data

To learn the basic principles of applying data exploration techniques, such as dimensionality projection and clustering on structured data.


Asking the right questions to understand the data.

Understanding how data visualization makes data clearer.

Performing Exploratory Data Analysis using PCA.

Clustering the data through K-means & DBSCAN clustering.

Evaluating the quality of clusters obtained.


Module 3: Prediction Methods - Regression

To understand the concept of linear regression and how it can be used with historical data to build models that can predict future outcomes.


The idea of regression and predicting a continuous output.

How do you build a model that best fits your data?

How do you quantify the degree of uncertainty?

What do you do when you don’t have enough data?

What lies beyond linear regression?


Module 4: Decision Systems

To understand the concept of classification and understand how tree-based models achieve prediction of outcomes that fall into two or more categories.


Understand the Decision Tree model and the mechanics behind its predictions.

Learn to evaluate the performance of classification models.

Understand the concepts of Ensemble Learning and Bagging.

Learn how Random Forests aggregate the predictions of multiple Decision Trees.


Module 5: Data Exploration - Unstructured Data

To understand the concept of Natural Language Processing and how natural language represents an example of unstructured data, the business applications for this kind of data analysis, and how data exploration and prediction are performed on natural language data.


Understand the concept of unstructured data, and how natural language is an example.

Understand the business applications for Natural Language Processing.

Learn the techniques and methods to analyze text data.

Apply the knowledge gained towards the business use case of sentiment analysis.


Module 6: Recommendation Systems

To understand the idea behind recommendation systems and potential business applications.


Learn the concept of recommendation systems and potential business applications.

Understand the sparse data problem that necessitates recommendation systems.

Learn about potentially simple solutions to the recommendation problem.

Understand the ideas behind Collaborative Filtering Recommendation Systems.


Module 7: Data Exploration - Temporal Data

To understand the critical concept of temporal data, and its differences from structured and unstructured data, the idea behind Time Series Forecasting and the preprocessing required to obtain stationarity in Time Series.


Understand temporal data and how it represents a different data modality.

Understand the idea behind Time Series forecasting

Learn about the concept of Stationary Time Series, testing for stationarity and conversion techniques to transform non-stationary time series into stationary.


Module 8: Prediction Methods - Neural Networks

To understand the ideas behind Neural Networks, their introduction of non-linearities into the encoding and predictive process through a hierarchical structure, and the various steps involved in their forward propagation and back propagation cycle to minimize prediction error.


Understand the key concepts involved in Neural Networks.

Learn about the encoding process taking place in the neural network layers, and how non-linearities are introduced.

Understand how the forward propagation happens through the layered architecture of neural networks and how the first prediction is achieved.

Learn about the cost function used to evaluate the neural network’s performance, and how gradient descent is used in a backpropagation cycle to minimize error.

Understand the critical optimization techniques used in gradient descent


Module 9: Computer Vision Methods

To understand how images represent a spatial form of unstructured data and hence, a different data modality, how the Convolutional Neural Network (CNN) structure achieves generalized encoding abilities from image data and acquire an understanding of what CNNs learn.


Learn about spatial concepts of images such as locality and translation invariance.

Understand the working of filters and convolutions, and how they achieve feature extraction to generate encodings.

Learn about how these concepts are used in the structure of Convolutional Neural Networks (CNNs) and understand what CNNs actually learn from image data.


Module 10: Workflows and Deployment

To obtain additional perspective on how the same takeaways from the conceptual modules discussed prior have been applied in various business scenarios and problem statements by industry leaders who have achieved success in practical applications of Data Science and AI.


Hands on project:

Insurance Claim Prediction

A key challenge for the insurance industry is to charge each customer an appropriate premium for the risk they represent. In this project, we will build a regression model to predict the cost of insurance claims using user information on age, gender, bmi, blood pressure, health conditions, as well as insurance claim details.

Tools & Concepts: Linear Regression, Model evaluation, Tuning, Exploratory Data Analysis, Python

Forecasting Monthly Sales of French Champagne

Being able to make accurate predictions of future revenue can be hugely important for businesses. This project will focus on forecasting the next monthly revenue of a french chamapagne brand, which will inform the decision-making process across all areas of the business, from purchasing decisions and marketing activity to staffing levels.

Tools & Concepts: Time Series Analysis, Predictive Modelling, Python for Time Series