CODEWARS : TOP 25 SQL INTERVIEW QUESTIONS TO ACE THE DATA ANALYST INTERVIEW

INTERVIEW TASK 01

TABLE SCHEMA

PEOPLE

CREATE FUNCTION agecalculator (d timestamp) 
RETURNS integer AS $$ --Dollar-quoted String It's something like quotation marks.
BEGIN
RETURN (SELECT EXTRACT(year FROM age(d)))::int;
END;
$$ LANGUAGE plpgsql;

INTERVIEW TASK 02

TABLE SCHEMA

DEPARTMENTS

SALES

SELECT id, name
FROM departments
WHERE EXISTS
(SELECT * FROM departments
INNER JOIN sales
ON departments.id=sales.department_id
WHERE price > 98.00)

INTERVIEW TASK 03

TABLE SCHEMA

PRODUCTS

SELECT id, name, stock FROM products WHERE producent='CompanyA' AND Stock <= 2 order by id asc;

INTERVIEW TASK04

  • percent growth rate can be negative
  • the percent growth rate should be rounded to one digit after the decimal point and immediately followed by a percent symbol “%”. See the desired output below for reference.

DESIRED OUTPUT

date       | count | percent_growth
-----------+-------+---------------
2016-02-01 | 175 | null
2016-03-01 | 338 | 93.1%
2016-04-01 | 345 | 2.1%
2016-05-01 | 295 | -14.5%
2016-06-01 | 330 | 11.9%
...
  • date – (DATE) first date of the month
  • count – (INT) a number of posts in a given month
  • percent_growth – (TEXT) a month-over-month growth rate expressed in percent
select date_trunc('month', created_at)::date as date,
count(*) as count,
round (
100.0 * (count(*) - lag(count(*), 1) over (order by date_trunc('month', created_at)::date asc)) /
lag(count(*), 1) over (order by date_trunc('month', created_at)::date asc)
,1 )
|| '%' as percent_growth
from posts
group by date
order by date asc;

INTERVIEW TASK06

TABLE SCHEMA

DEMOGRAPHICS

select count(*) as count, race from demographics group by race order by count desc;

INTERVIEW TASK07

TABLE SCHEMAS

PEOPLE

select age, count(*) as people_count from people group by age;

INTERVIEW TASK08

TABLE SCHEMA

EVENTS

Select date_trunc('day',created_at) as day , description , Count(*) as Count  From events  where name = ‘trained' 
Group by date_trunc('day',created_at),description

INTERVIEW TASK09

TABLE SCHEMA

PEOPLE

SELECT age, COUNT(*) AS total_people FROM people GROUP BY age 
HAVING COUNT(*) >= 10;

INTERVIEW TASK10

TABLE SCHEMAS

DEPARTMENTS

SALES

SELECT id, name FROM departments WHERE id IN (SELECT department_id FROM sales WHERE (price) > 98.00)

INTERVIEW TASK11

TABLE SCHEMA

PRODUCTS

COMPANIES

SELECT p.id, p.name , p.isbn , p.company_id , p.price , c.name AS company_name FROM products AS p 
LEFT JOIN companies AS c
ON p.company_id = c.id

INTERVIEW TASK12

TABLE SCHEMAS

PEOPLE

SALES

SELECT people.id,people.name, count(sales.id) AS sale_count, 
rank () OVER ( ORDER BY count(sales.id) desc ) sale_rank
FROM people JOIN sales ON people.id = people_id
GROUP BY people.id
order by sale_count desc,people.id asc;

INTERVIEW TASK13

TABLE SCHEMAS

PEOPLE

TOYS

SELECT p.id, p.name, count(*) as toy_count FROM people p JOIN toys t 
ON p.id = t.people_id
GROUP BY p.id, p.name;

INTERVIEW TASK14

TABLE SCHEMA

STUDENT

RESULT

SELECT min(score),
ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY score)::numeric, 2)::float AS median,
max(score)
FROM result;

INTERVIEW TASK15

TABLE SCHEMA

PEOPLE

SELECT min(age) AS age_min, max(age) AS age_max FROM  people;

INTERVIEW TASK16

TABLE SCHEMA

DECIMALS

SELECT mod(number1, number2) AS mod
FROM decimals;

INTERVIEW TASK17

TABLE SCHEMA

PRODUCTS

DETAILS

CREATE EXTENSION tablefunc;SELECT * FROM  crosstab (      'SELECT p.name, detail, COUNT(d.id)      FROM products p      JOIN details d      ON p.id = d.product_id      GROUP BY p.name, d.detail      ORDER BY 1,2')AS ct (name text, bad bigint, good bigint, ok bigint)

INTERVIEW TASK18

TABLE SCHEMA

select t.login::date as day, d.name as department_name, sum(date_part('hour', age(t.logout, t.login))) as total_hours
from timesheet t
Inner join department d
on t.department_id = d.id
group by day, department_name
order by day, department_name

INTERVIEW TASK19

  • customer_id (int4)
  • email (varchar)
  • payments_count (int)
  • total_amount (float)
select c.customer_id as customer_id, c.email as email, count(*) as payments_count, 
(sum(p.amount)::float)as total_amount
from customer c join payment p
on
c.customer_id = p.customer_id
GROUP BY c.customer_id, c.email
ORDER BY total_amount desc
fetch first 10 rows only

INTERVIEW TASK20

TABLE SCHEMA

DECIMALS

SELECT trunc(number1 + number2) AS towardzero
FROM decimals;

INTERVIEW TASK21

TABLE FILM_ACTOR

Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id | smallint | not null
film_id | smallint | not null
...

TABLE ACTOR

Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id | integer | not null
first_name | character varying(45) | not null
last_name | character varying(45) | not null
...

TABLE FILM

Column     | Type                        | Modifiers
------------+-----------------------------+----------
film_id | integer | not null
title | character varying(255) | not null
...

THE DESIRED OUTPUT:

first_actor | second_actor | title
------------+--------------+--------------------
John Doe | Jane Doe | The Best Movie Ever
...
  • first_actor – Full name (First name + Last name separated by a space)
  • second_actor – Full name (First name + Last name separated by a space)
  • title – Movie title
-- Replace with your SQL Queryselect 
Select
concat(a1.first_name, ' ', a1.last_name) as second_actor,
concat(a2.first_name, ' ', a2.last_name) as first_actor,
f.title
from (
select
fa1.actor_id as actor_a_id,
fa2.actor_id as actor_b_id,
count(fa1.film_id) as casted_together,
array_agg(fa1.film_id) as film_ids
from film_actor fa1
join film_actor fa2
on fa1.film_id = fa2.film_id
and fa1.actor_id > fa2.actor_id
join film f
on fa1.film_id = f.film_id
group by fa1.actor_id, fa2.actor_id
order by casted_together desc
limit 1
) s
join actor a1 on a1.actor_id = s.actor_a_id
join actor a2 on a2.actor_id = s.actor_b_id
join film f on f.film_id = any(s.film_ids)

INTERVIEW TASK22

TABLE SCHEMAS

USSALES,EUSALES

ALTER TABLE ussales
ADD COLUMN location varchar(255)
DEFAULT 'US';
ALTER TABLE eusales
ADD COLUMN location varchar(255)
DEFAULT 'EU';
SELECT * FROM ussales
WHERE price > 50.00
UNION ALL
SELECT * FROM eusales
WHERE price > 50.00
ORDER BY location desc, id;

INTERVIEW TASK23

TABLE SCHEMA

SALES

CREATE VIEW members_approved_for_voucher ASSELECT m.id, m.name, m.email, SUM(p.price) as total_spending
FROM members m, sales s, products p
WHERE m.id = s.member_id
AND s.product_id = p.id
AND s.department_id IN (
SELECT s.department_id
FROM products p, sales s
WHERE s.product_id = p.id
GROUP BY s.department_id
HAVING SUM(p.price) > 10000
)GROUP BY m.id
HAVING SUM(p.price) > 1000
ORDER BY m.id;
SELECT * from members_approved_for_voucher;

INTERVIEW TASK24

TABLE SCHEMA

PEOPLE

SELECT * FROM people
WHERE age > 50
ORDER BY age DESC;

INTERVIEW TASK25

TABLE SCHEMA

DEPARTMENTS

SALES

WITH special_sales AS (SELECT * FROM sales WHERE (price) > 90.00)
SELECT id, name FROM departments
WHERE id IN ( SELECT department_id
FROM sales
WHERE (price) > 90.00);

--

--

--

I’m Rajeev, Tableau Zen Master ,Tableau Ambassador, Tableau Featured Author, Data Evangelist from India.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Integrating MS Excel and SSRS for Project Control

Pixel Shaders in Framer X

How to excel in engineering management

GitLab includes utilities that allows us to create pipelines that can be used for CI (Continuous…

Store JSON Data in Shared Pref

Containers & Containerization

Using State Machines in Laravel

A Junior Engineer’s Review of Triplebyte

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Rajeev Pandey

Rajeev Pandey

I’m Rajeev, Tableau Zen Master ,Tableau Ambassador, Tableau Featured Author, Data Evangelist from India.

More from Medium

8 Weeks SQL Challenge: Case Study #2 — Pizza Runner

Advanced SQL for analysts: window functions are not your enemies, P.1

Top 10 Must-Know SQL Queries For Data Analysis

SQL — 101