Current Openings

Currently we are hiring for following positions :
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

Business applications are often modeled as transactional systems, where each transaction performs a specific action (usually update) upon a database with a defined schema.

Further, most transactions have a form type user interface, where the user fills in desired data and hits a button to send the data to the server. Validations may be performed upon the data submitted by the user before sending it to the server.

Given below is a simple example schema for an inventory database:

                                create table products (
                                    id integer,
                                    name text,
                                    PRIMARY KEY(id)
                                );
                            
                                create table customers (
                                    id integer,
                                    name text,
                                    address1 text,
                                    address2 text,
                                    email_id text,
                                    phone text,
                                    PRIMARY KEY(id)
                                );
                            
                                create table suppliers (
                                    id integer,
                                    name text,
                                    address1 text,
                                    address2 text,
                                    email_id text,
                                    phone text,
                                    PRIMARY KEY(id)
                                );
                            
                                create table sales (
                                    id integer,
                                    customer_id integer,
                                    date date,
                                    PRIMARY KEY(id),
                                    CONSTRAINT fkey_customer
                                        FOREIGN KEY(customer_id)
                                            REFERENCES customers(id)
                                );
                            
                                create table sales_items(
                                    sales_id integer,
                                    product_id integer,
                                    qty integer,
                                    sale_price integer,
                                    CONSTRAINT fkey_sales
                                        FOREIGN KEY(sales_id)
                                            REFERENCES sales(id),
                                    CONSTRAINT fkey_products
                                        FOREIGN KEY(product_id)
                                            REFERENCES products(id)
                                );
                            
                                create table purchases (
                                    id integer,
                                    vendor_id integer,
                                    date date,
                                    PRIMARY KEY(id),
                                    CONSTRAINT fkey_vendor
                                        FOREIGN KEY(vendor_id)
                                            REFERENCES suppliers(id)
                                );
                            
                                create table purchase_items (
                                    purchase_id integer,
                                    product_id integer,
                                    qty integer,
                                    purchase_price integer,
                                    CONSTRAINT fkey_purchase
                                        FOREIGN KEY(purchase_id)
                                            REFERENCES purchases(id),
                                    CONSTRAINT fkey_products
                                        FOREIGN KEY(product_id)
                                            REFERENCES products(id)
                                );
                            
                            insert into products values (101, 'product1');
                            insert into products values (102, 'product2');
                            insert into products values (103, 'product3');
                            insert into products values (104, 'product4');
                            
                            insert into customers values (201, 'customer1', 'noida', 'delhi', 'customer1@xyz.com', '0123456');
                            insert into customers values (202, 'customer2', 'faridabad', 'gurugram', 'customer2@xyz.com', '0159753');
                            insert into customers values (203, 'customer3', 'gurugram', 'delhi', 'customer3@xyz.com', '0147852');
                            
                            insert into suppliers values (301, 'supplier1', 'ghaziabad', 'noida', 'supplier1@xyz.com', '0128452');
                            insert into suppliers values (302, 'supplier2', 'sonipat', 'gurugram', 'supplier2@xyz.com', '0179793');
                            insert into suppliers values (303, 'supplier3', 'surat', 'nagpur', 'supplier3@xyz.com', '0117855');
                        

This simple database keeps a record of all sales and purchases by customer and vendor, updating the relevant tables as transactions are done.

NOTE - The schema dump provided is compatible with PostgreSQL database, You can change schema according to database type.

Question 1

Write stored procedures in SQL to implement the purchase and sale transactions.

The purchase transaction inputs are : vendor_id, date, product_id, qty, purchase_price ...]
// more than one product can be purchased at a time from a vendor

The purchase transaction creates a record of the purchase in appropriate tables.

The sale transaction inputs are : customer_id, date, product_id, qty, sale_price ...]
// more than one product can be sold at a time to a customer.

The sale transaction creates a record of the sale in appropriate tables.

You can assume a given set of products, customers, and vendors and make sample data on your own to test the transactions.

Submit a snapshot of your database with sample data, and the results of executing sale and purchase transactions.

Question 2

Write a stored procedure to generate a stock report for a date range.

                            select stock_report (date1 date, date2 date);
                            

This report shows the following columns

                            product_id product_name opening_balance qty_in qty_out qty_net closing_balance_date2
                            

The report calculates opening balances from "beginning of time" to date1, then computes qty_in and qty_out using purchases and sales respectively, then calculates qty_net by qty_in and qty_out between date1 and date2 and finally computes the closing balance on date2.

You may need to write multiple sql statements inside the body of the stock_report stored procedure.

You can use any language for database programming, including PLV8 (embeddeded javascript), PL/SQL or any other embedded language embedded in a SQL server.

Bonus : Can you make the report more efficient ?

Submission Guidelines

To submit your work send your running code in text file and mail to careers@saralweb.com with subject "SDE Assignment - Nov 2022" .

Submission Deadline

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