We all know how to get a distinct count of a column. It can be done with COUNT(DISTINCT column_name). For example:
|
1 2 3 4 |
SELECT COUNT(DISTINCT user_id) FROM web_store |
Outputs:
|
1 2 3 4 5 |
COUNT(DISTINCT user_id) ------------------------- 6 |
In this post let’s look at various approaches to count unique values. Using DISTINCT, … Read more ›...
Q01. What is the difference between map and flatMap operations in Spark?
A01. The map and flatMap are transformation operations in Spark.
map transformation is applied to each element of RDD or a DataFrame and it returns the result as a new RDD or a DataFrame.
…
Q138. What are the basic considerations when building a data lake on an AWS platform?
A138.
1. Ingestion into S3 (i.e. Simple Storage Service) from different source systems. E.g. csv/xml/json files extracted from source systems, streamed data from source systems via message queues/topics,
…
This extends Common Table Expressions (i.e. CTE) in SQL using the “WITH” clause. You can try the example on DB Fiddle. Use MySQL v8.0 or later. Scenario: 1 Given a table like this with each entry with two dates (i.e. from_date & … Read more ›...
This extends PySpark map vs flatMap Interview Q&As with tutorials. Q01. What is a Row object in PySpark? A01. Row can be used to create a row object by using named arguments. The previous example can be represented with a Row object. … Read more ›...
One of the common SQL interview questions is to quiz your understanding of SQL joins. In this let’s look at joins example with two scenarios.
Venn Diagram
Joins can be specified as a venn diagram shown below:
But, it gets a bit more tricky when you consider the below scenarios:
Scenario 1: When both input tables don’t have duplicate values.
…
If you want to learn the basics of SQL analytics function, first read SQL analytic functions interview questions – Part 1 | SQL analytic functions interview questions – Part 2 | SQL analytic functions interview questions – Part 3
Given the below data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
event_id event_type city country duration_seconds ---------------------------------------------------------------------- 1 login sydney australia 9 2 logout melbourne australia 8 3 login sydney australia 5 4 login melbourne australia 9 5 login california usa 8 6 logout new york usa 6 7 login california usa 5 8 login new york usa 2 |
Q1.
…
The below SQL problem can be practiced on MySQL database at db-fiddle or database of your choice. Q1. Given the below data, how will you find the last non null values for each event_id?
|
1 2 3 4 5 6 7 8 9 10 |
event_id date city country event_type --------------------------------------------------------------------- 1 2023-02-02 sydney australia 0 1 2023-02-03 null australia 1 1 2023-02-04 melbourne australia 2 2 2023-02-06 california usa 1 2 2023-02-07 new york usa null 2 2023-02-08 null usa 4 |
Create table & insert data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DROP TABLE IF EXISTS event; CREATE TABLE IF NOT EXISTS event ( event_id int, date date, city varchar(20), country varchar(20), event_type varchar(20) ); INSERT INTO event(event_id,date,city,country,event_type) VALUES (1, '2023-02-02', 'sydney', 'australia', '0' ); INSERT INTO event(event_id,date,city,country,event_type) VALUES (1, '2023-02-03', null, 'australia', '1' ); INSERT INTO event(event_id,date,city,country,event_type) VALUES (1, '2023-02-04', 'melbourne', 'australia', '2' ); INSERT INTO event(event_id,date,city,country,event_type) VALUES (2, '2023-02-06', 'california', 'usa', '1' ); INSERT INTO event(event_id,date,city,country,event_type) VALUES (2, '2023-02-07', 'new york', 'usa', null ); INSERT INTO event(event_id,date,city,country,event_type) VALUES (2, '2023-02-08', null, 'usa', '4'); |
Solution In order to ignore null values “CASE WHEN...
SQL interview Questions & Answers is a must for any developer as all non-trivial applications need to talk to a database with CRUD operations. Q3 – Q15 are very popular with the interviewers. If you want to quickly practice your SQL skills try DB Fiddle or install MySQL locally as...
This continues 25+ SQL interview questions & answers – beginner
Q16. Why do you have CASE statements in SQL?
A16. CASE statements in SQL are similar to IF and ELSE conditions in programming languages. CASE statements are used to fetch particular values based on certain conditions.
…
Setup the data on db-fiddle MySQL V8.0 to practice. This question is based on the LeetCode SQL problem Q. Given the below input: login table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
user_id date ----------------------- 1 2023-01-01 1 2023-01-02 1 2023-01-04 2 2023-01-01 2 2023-01-02 3 2023-01-09 4 2023-01-09 5 2023-01-12 6 2023-01-15 7 2023-01-18 |
txn table
|
1 2 3 4 5 6 7 8 9 10 11 12 |
user_id date amount --------------------------------- 1 2023-01-01 75.00 1 2023-01-04 95.00 2 2023-01-02 60.00 3 2023-01-09 25.00 4 2023-01-09 25.00 5 2023-01-12 63.00 5 2023-01-12 74.00 5 2023-01-12 82.00 |
how will you write SQL query to output: 1) How many users who logged in did not do any transactions? …...
The below SQL problem can be practiced on MySQL database at db-fiddle or database of your choice. Use latest version from the drop down.
Q1. Given the below data, how will you get the individual sum of the count by score and cumulative sum over the whole data?
…
The data analysts & engineers will often be faced with the below SQL scenario at work. It is also often asked in job interviews. This solution uses SQL analytical functions & you can learn more about them at SQL analytic functions interview questions – Part 1
Setup the data on db-fiddle MySQL V8.0 to practice.
…
I have a good news, I got job offer. I like to share with you from my bottom of my heart feeling thankful to you.