Saral Web Noida Software Development Engineer : saralweb.com

Organisation : Saral Web
Designation : Software Development Engineer (SDE)
Qualification : Graduate or above in Computer Science / Engineering
Job Location : Noida (UP) India
Website : https://www.saralweb.com/careers.html

Current Openings in Saral Web

Currently we are hiring for following position :

1.Software Development Engineer (SDE)

Location : Noida (UP) India
Education : Graduate or above in computer science / engineering from a recognized institute

Job Description:
** Complete feature ownership including user interface, usability feedback incorporation, and quality control
** The ideal candidate will be able to commit long term to deep specialized feature areas, develop in-depth expertize and insight into feature usage.
** Preference will be given to candidates with good oral and written communication skills and job responsibilities require work closely with design and marketing staff

Candidate Requirements:
** Exposure to UNIX/LINUX.
** Exposure to NodeJS, RDBMS (Postgresql Preferred)
** Exposure to HTML, CSS, Javascript, Web Technologies, Dart, Flutter, C, C++, python
** Clear understanding of Data Structures, Algorithms and Object Oriented System Design.

Interview Challenge :
Reconciliation of tables in SQL
Consider two tables: invoices and payments. The invoices table contains records of invoices raised, and the payments table contains records of payments received.

Invoices:
id | date | cname | amount
—-+————+——–+——–
1 | 2021-12-12 | cname1 | 10000
2 | 2021-12-13 | cname2 | 5000
3 | 2022-01-15 | cname1 | 7000
4 | 2022-01-16 | cname2 | 1000

Payments :
id | date | cname | amount
—-+————+——–+——–
1 | 2022-01-05 | cname1 | 5000
2 | 2022-01-07 | cname2 | 5000
3 | 2022-02-05 | cname1 | 10000
4 | 2022-02-06 | cname2 | 1000

Calculate Customer Balances as of a Date:
Q.1) Write a SQL Query which produces the following output (when given an input date of 28/2/2022).

cname | invoiced | paid | due
——–+———-+——-+——
cname1 | 17000 | 15000 | 2000
cname2 | 6000 | 6000 | 0

Note:
** Please make sure to write just one SQL statement to produce the above result.
** Please do NOT write a stored procedure
** The input date may be hard coded into the SQL statement.
** You should test your query with other dates also, such as 31/1/2022 and include the result in your submission.

Calculate Running Balances:
Q.2) Extend the SQL query to do invoice / payment matching as follows (as of 28/2/2022)

Matching:
date | document_id | cname | amount | due
———————+————-+——–+——–+——-
2021-12-12 00:00:00 | 1 | cname1 | 10000 | 10000
2022-01-05 00:00:00 | 1 | cname1 | -5000 | 5000
2022-01-15 00:00:00 | 3 | cname1 | 7000 | 12000
2022-02-05 00:00:00 | 3 | cname1 | -10000 | 2000
2021-12-13 00:00:00 | 2 | cname2 | 5000 | 5000
2022-01-07 00:00:00 | 2 | cname2 | -5000 |** 0
2022-01-16 00:00:00 | 4 | cname2 | 1000 | 1000
2022-02-06 00:00:00 | 4 | cname2 | -1000 |** 0

Your answers should include the following:
** Create table commands for invoices and payment tables
** Sample data creation commands for both tables
** SQL query to generate the result for Q1 (read instructions in question)
** SQL query to generate the result for Q2 (read instructions in question)

Submission Guidelines :
To submit your work send your running code in text file and mail to careers [AT] saralweb.com with subject “SDE Assignment – Apr 2022”.

Submission Deadline :
Candidates will be considered in the order of assignment submission. Please submit your assignment as soon as possible.

Office Address :

Saral Web
6th Floor, Tower A,
Stellar IT Park, C-25 Sector 62,
Noida, 201309