Jul 7

#100DaysofSQL | DAY 20: How to populate of all field of duplicated customer_id

When? Identify primary / composite keys OR check tables for unintended duplicates How? (1) Identify duplicated cust_id (2) Use INNER JOIN to identify matching rows between raw table and (1) Query to use: Method 1: (using CTE) WITH dupes AS ( SELECT user_id as dupe_id, COUNT(*) FROM users GROUP BY user_id HAVING COUNT(*) > 1 ) SELECT user_id, full_name, email FROM users INNER JOIN dupes on user_id = dupe_id

1 min read

#100DaysofSQL | DAY 20: How to populate of all field of duplicated customer_id

From https://jonmce.medium.com/the-day-one-sql-query-we-should-all-know-and-love-31ba97ecb2b1

When? Identify primary / composite keys OR check tables for unintended duplicates
How? (1) Identify duplicated cust_id (2) Use INNER JOIN to identify matching rows between raw table and (1)
Query to use:

Method 1: (using CTE)

WITH dupes AS (
  SELECT user_id as dupe_id, COUNT(*)
  FROM users
  GROUP BY user_id
  HAVING COUNT(*) > 1
               )
SELECT user_id,
       full_name,
       email
FROM users INNER JOIN dupes on user_id = dupe_id

Method 2:

SELECT users.user_id, full_name, email 
FROM users
INNER JOIN (
   SELECT user_id, COUNT(*)
   FROM users
   GROUP BY user_id
   HAVING COUNT(*) > 1
   ) AS dupes 
ON users.user_id = dupes.user_id

Jul 7

#100DaysofSQL | DAY 19: How to check number of times a row is repeated?

I should really be more diligent in posting more often, one of my friends joked about how my #100daysofSQL will take 365 days (instead of 100days) to complete🤣 Thanks to this article, (and after also seeing this a million times across other articles), I finally understood the value of this: …

1 min read

Jul 4

#100DaysofSQL | DAY 18: Assigning trx sequence for each unique customer

Upon trying to calculate the replenishment rate of one product franchise group, here I learn how to assign transaction sequence for each unique customer via here. When? Assign transaction sequence for each unique customer/student from using transaction lines/subject<>marks (with repeated rows of customer codes) *Note: Examples below taken from here SO in fact for my work purposes dealing with sales<> transaction data

1 min read

#100DaysofSQL | DAY 18: Assigning trx sequence for each unique customer

Upon trying to calculate the replenishment rate of one product franchise group, here I learn how to assign transaction sequence for each unique customer via here.

When? Assign transaction sequence for each unique customer/student from using transaction lines/subject<>marks (with repeated rows of customer codes)

*Note: Examples below taken from here SO in fact for my work purposes dealing with sales<> transaction data

StudentName = Customer_code
Subject = Date

Jun 14

#100DaysofSQL | DAY 17: Alternatives to DISTINCT!

After using SQL for a while at work + reading medium articles, I realise that DISTINCT syntax does not always remove all duplicates as expected. According to this article, there are other 3 alternative ways! “DISTINCT” does not tell you if the JOINs and filters you used are correct or…

100 Daysofcode

1 min read

#100DaysofSQL | DAY 17: Alternatives to DISTINCT!

Jun 2

#100DaysofSQL | DAY 16: Window Functions are not scary!

Read some interesting articles, namely this, this and this over the weekend, thus I would like to document some interesting findings :) When? Complete simple calculations via easy computing of dynamic aggregate values Query to use SELECT s.*, AVG(score) OVER (PARTITION BY faculty) AS avg_score, MIN(score) OVER (PARTITION BY faculty) AS min_score, MAX(score) OVER (PARTITION BY faculty) AS max_score FROM students_score s

1 min read

#100DaysofSQL | DAY 17: Window Functions are not scary!

#100DaysofSQL | DAY 16: Window Functions are not scary!

Read some interesting articles, namely this, this and this over the weekend, thus I would like to document some interesting findings :)

When? Complete simple calculations via easy computing of dynamic aggregate values
Query to use

SELECT s.*, 
    AVG(score) OVER (PARTITION BY faculty) AS avg_score,
    MIN(score) OVER (PARTITION BY faculty) AS min_score,
    MAX(score) OVER (PARTITION BY faculty) AS max_score
FROM students_score s

https://medium.com/@massomi/advanced-sql-for-analysts-window-functions-are-not-your-enemies-p-1-8731fe187092

Jun 2

#100DaysofSQL | DAY 15: Coalesce function in SQL

Read some interesting articles, namely this, this and this. over the weekend, thus I would like to document some interesting findings :) When? For data cleaning → replace NULL values in table Query to use SELECT COALESCE(column_name, 'Missing value') as Location FROM employee;

1 min read

#100DaysofSQL | DAY 15: Coalesce function in SQL

Read some interesting articles, namely this, this and this. over the weekend, thus I would like to document some interesting findings :)

When? For data cleaning → replace NULL values in table
Query to use

SELECT COALESCE(column_name, 'Missing value') as Location
FROM employee;

https://medium.com/@ashbabkhan12/coalesce-function-in-sql-fa7a17bf8953

Jun 2

#100DaysofSQL | DAY 14: Identify deplicated rows in table

Read some interesting articles, namely this, this and this. over the weekend, thus I would like to document some interesting findings :) When? Especially relevant when analyzing transaction lines against customer table! Since 1 cust = many transactions = many transaction lines Query to use SELECT Day, Sales, COUNT(*) -- count repeated rows FROM df2 GROUP BY Day, Sales -- used to sequence data HAVING COUNT(*)>1 -- identify repeated rows

1 min read

#100DaysofSQL | DAY 14: Identify deplicated rows in table

Read some interesting articles, namely this, this and this. over the weekend, thus I would like to document some interesting findings :)

When? Especially relevant when analyzing transaction lines against customer table! Since 1 cust = many transactions = many transaction lines

Query to use

SELECT Day, Sales, COUNT(*) -- count repeated rows
FROM df2 
GROUP BY Day, Sales -- used to sequence data 
HAVING COUNT(*)>1 -- identify repeated rows

May 26

#100DaysofSQL | DAY 13: SQL Operators

My bad, I totally forgot to update here, but I am remember from now on! I share from interesting learnings from this article! Arithmetic Operators Creation of new columns via +, -, x, / means! SELECT Colname1 + Colname2 AS combinedname FROM Tablename Especially useful when dealing with columns with numerical data!

100 Daysofcode

1 min read

#100DaysofSQL | DAY 13: SQL Operators

My bad, I totally forgot to update here, but I am remember from now on!

I share from interesting learnings from this article!

Arithmetic Operators

Creation of new columns via +, -, x, / means!

SELECT Colname1 + Colname2 AS combinedname
FROM Tablename

Especially useful when dealing with columns with numerical data!

May 6

#100DaysofSQL | DAY 12: Data Cleansing

Today I came across this article detailing the most commonly used syntax used for data cleansing — I share the ones I’m not so familiar with (1) CAST → Used to convert data types, mostly used to convert to numerical values prior to aggregation calculations CAST(column_name AS data_type) -- Method…

Sql

2 min read

May 3

#100DaysofSQL | DAY 11: Best SQL Practices

Today I came across this informative article: SQL Best Practices and decided to document the ones which I found interesting! Tip 1: Increase readability by using In-line & Block comments

Sql

2 min read