## Apple Interview Question

Country: United States

Comment hidden because of low score. Click to expand.
0
of 0 vote

``````create table table_1 (
factory varchar(5),
material char(1),
qty int,
date datetime
);

create table table_2 (
factory varchar(5),
company varchar(5),
material char(1),
qty int,
date datetime
);

insert into table_1 values ('f1', 'a', 100, '2010-10-10'), ('f1', 'b', 200, '2010-10-10' ), ('f1', 'a', 500, '2011-11-11' ),('f2', 'a', 300, '2010-10-10');
insert into table_2 values ('f1', 'c1', 'a', 100, '2010-10-12'), ('f1', 'c2', 'b', 200, '2010-10-10' ), ('f2', 'c1', 'a', 500, '2011-11-11' ),('f2', 'c3', 'a', 300, '2010-10-10');

with cfmq as (
select  company, material, qty, date, factory, sum(qty) over (partition by factory, material order by date, qty, company) as qty_sum
from table_2 order by date, qty, company
)select company, material, cfmq.qty, cfmq.date, factory, table_1.qty-cfmq.qty_sum from cfmq join table_1 using (factory, material) where table_1.date <= cfmq.date  and (table_1.qty-cfmq.qty_sum) >= 0;``````

Comment hidden because of low score. Click to expand.
0

THERE ARE 3 TABLES GIVEN, AND YOU SHOULD OUTPUT WHICH FACTORY SUPPLIES WHAT AMOUNT TO EACH ORDER. THERE MIGHT BE DUPLICATED RESULTS IN TERMS OF ORDER BECAUSE ONE OREDER MIGHT BE FULFILLED BY MULTIPLE FACTORIES.

Comment hidden because of low score. Click to expand.
0
of 0 vote

I think it is impossible to avoid duplicate results by writing only a query.
You can implement this by writing a stored procedure and using two temporary tables in it (although you can use one)

Comment hidden because of low score. Click to expand.
0
of 0 vote

with cfmq as (
select company, material, qty, date, factory, sum(qty) over (partition by factory, material order by date, qty, company) as qty_sum
from table_2 order by date, qty, company
)select company, material, cfmq.qty, cfmq.date, factory, table_1.qty-cfmq.qty_sum from cfmq join table_1 using (factory, material) where table_1.date <= cfmq.date and (table_1.qty-cfmq.qty_sum) >= 0;

Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

### Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

### Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.