Sng Ying

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

--

--

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
Raw data

--

--

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

--

--

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!

--

--

Sng Ying

Sng Ying

Aspiring analyst wanna-be happy to connect with fellow data enthusiasts :)