Tech (Companion) Q&As Archives | Page 8 of 14 | 800+ Java & Big Data Interview Q&As

Blog Archives

« 1 … 4 5 6 7 8 9 10 11 12 … 14 »

24: 50+ SQL scenarios based interview Q&As – Window Query

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:


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

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.

…

25: 50+ SQL scenarios based interview Q&As – get the last non null value

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?


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

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

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');

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...

This content is for 30-Day-Full-Access, 100-Day-Full-Access, 200-Day-Full-Access, and 365-Day-Full-Access members only. Register 80+ Free Java FAQs 80+ Free Big Data FAQs

Already a member? Log in here

25+ SQL interview questions & answers – beginner

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 content is for 30-Day-Full-Access, 100-Day-Full-Access, 200-Day-Full-Access, and 365-Day-Full-Access members only. Register 80+ Free Java FAQs 80+ Free Big Data FAQs

Already a member? Log in here

25+ SQL interview questions & answers – intermediate to experienced

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.

…

26: 50+ SQL scenarios based interview Q&As – number of logins by transaction count

SQL scenario based Q&As - problem solving

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


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

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


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

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? …...

This content is for 30-Day-Full-Access, 100-Day-Full-Access, 200-Day-Full-Access, and 365-Day-Full-Access members only. Register 80+ Free Java FAQs 80+ Free Big Data FAQs

Already a member? Log in here

26: 50+ SQL scenarios based interview Q&As – Window function (i.e. OVER(…)) with GROUP BY

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?

…

27: 50+ SQL scenarios based interview Q&As – identifying consecutive records – job status repetition

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.

…

28: 50+ SQL scenarios based interview Q&As – identifying consecutive logins

This extends 50+ SQL scenarios based interview Q&As – identifying consecutive records – job status repetition. A popular SQL coding interview question. Setup the data on db-fiddle MySQL V8.0 to practice. Q. Given the below input, how will you list user_id & … Read more ›...

This content is for 30-Day-Full-Access, 100-Day-Full-Access, 200-Day-Full-Access, and 365-Day-Full-Access members only. Register 80+ Free Java FAQs 80+ Free Big Data FAQs

Already a member? Log in here

29: 50+ SQL scenarios based interview Q&As – Non-Equi Join problem

Q01. What is a Non-Equi Join? A01. A Non-Equi Join in SQL that uses operators other than equality operator (i.e. ‘=’), like , = to join two tables. Q02. When to use a Non-Equi Join? … Read more ›...

This content is for 30-Day-Full-Access, 100-Day-Full-Access, 200-Day-Full-Access, and 365-Day-Full-Access members only. Register 80+ Free Java FAQs 80+ Free Big Data FAQs

Already a member? Log in here

3. Understanding HBase (NoSQL) database basics in 7 steps

Extends Getting started with HBase. HBase is a NoSQL database. HBase is a columnar database. #1. In HBase, you create the Table & column-families “create” command to create a table with column families. You can later on use “ … Read more ›...

This content is for 30-Day-Full-Access, 100-Day-Full-Access, 200-Day-Full-Access, and 365-Day-Full-Access members only. Register 80+ Free Java FAQs 80+ Free Big Data FAQs

Already a member? Log in here

30: 50+ SQL scenarios based interview Q&As – Data cleaning, quality checks & profiling

This post covers SQL examples to perform quality checks, clean data and profile data. Q01. What is Data Cleaning? Can SQL do data cleaning? A01. Data cleaning is the process of fixing bad data. … Read more ›...

This content is for 30-Day-Full-Access, 100-Day-Full-Access, 200-Day-Full-Access, and 365-Day-Full-Access members only. Register 80+ Free Java FAQs 80+ Free Big Data FAQs

Already a member? Log in here

31: 50+ SQL scenarios based interview Q&As – Finding records from first table where no match found in second table

Setup the data on db-fiddle MySQL V9.0 to practice. There are business scenarios for:

1) Finding records in the first table where matching records are found in the second table are shown in the output. This can be done with a semi-join.

…

32: 50+ SQL scenarios based interview Q&As – use of unpivot, lag(..), row_num(..), cumulative sum() and CTE

Setup the data on db-fiddle MySQL V9.0 to practice. Make sure to select latest MySQL version to ensure Common Table Expressions (i.e CTE) is supported. This is not a real life scenario problem, but a simple example that uses many of the SQL syntaxes & techniques. … Read more ›...

This content is for 30-Day-Full-Access, 100-Day-Full-Access, 200-Day-Full-Access, and 365-Day-Full-Access members only. Register 80+ Free Java FAQs 80+ Free Big Data FAQs

Already a member? Log in here

« 1 … 4 5 6 7 8 9 10 11 12 … 14 »

Blog Archives

24: 50+ SQL scenarios based interview Q&As – Window Query

25: 50+ SQL scenarios based interview Q&As – get the last non null value

25+ SQL interview questions & answers – beginner

25+ SQL interview questions & answers – intermediate to experienced

26: 50+ SQL scenarios based interview Q&As – number of logins by transaction count

26: 50+ SQL scenarios based interview Q&As – Window function (i.e. OVER(…)) with GROUP BY

27: 50+ SQL scenarios based interview Q&As – identifying consecutive records – job status repetition

28: 50+ SQL scenarios based interview Q&As – identifying consecutive logins

29: 50+ SQL scenarios based interview Q&As – Non-Equi Join problem

3. Understanding HBase (NoSQL) database basics in 7 steps

30: 50+ SQL scenarios based interview Q&As – Data cleaning, quality checks & profiling

31: 50+ SQL scenarios based interview Q&As – Finding records from first table where no match found in second table

32: 50+ SQL scenarios based interview Q&As – use of unpivot, lag(..), row_num(..), cumulative sum() and CTE

300+ Java Interview Questions - FAQs

10+ Companion Techs Interview Q&As

300+ Big Data Interview Questions - FAQs

16+ Java Tech Key Areas Interview Q&As

Your Career Tips to go places

Tutorials on Java & Big Data

300+ Java Interview Questions Answered

Disclaimer