Live Big Data Mock Interview | Techno-Managerial Round | Scenario Based Questions

[@Sudeep-ow4pe]

Great interview, interviwer is very polite also explained the solution of questions.

[@hdr-tech4350]

Date format, sum case, group by
Array max occured number

[@sumesh1993]

My attempt to first SQL question (Postgres)
create table transactions_new
(
id int,
country varchar,
state varchar,
amount int,
trans_date date
);
insert into transactions_new values (121, 'US','approved', 1000, '2018-12-18');
insert into transactions_new values (122, 'US','declined', 2000, '2018-12-19');
insert into transactions_new values (123, 'US','approved', 2000, '2019-01-01');
insert into transactions_new values (124, 'DE','approved', 2000, '2019-01-07');
********************************************************************************************************
select distinct
concat(date_part('month',trans_date),'-',date_part('year',trans_date)) as date,
country,
count(state) over (partition by concat(date_part('month',trans_date),'-',date_part('year',trans_date)), country) as trans_count,
count(state) filter (where state = 'approved') over (partition by concat(date_part('month',trans_date),'-',date_part('year',trans_date)), country) as approved_count,
sum(amount) over (partition by concat(date_part('month',trans_date),'-',date_part('year',trans_date)), country) as trans_total_amt,
sum(amount) filter (where state = 'approved') over (partition by concat(date_part('month',trans_date),'-',date_part('year',trans_date)), country) as approved_total_amt
from
transactions_new;