Thanks again for applying to the Application Engineer job at ******! The purpose of this gist is to get a better sense of your technical skills and overall communication style. Take as much time as you need to answer these questions.
Engineers at ****** communicate primarily in written form, via ****** Issues and Pull Requests. We expect our engineers to communicate clearly and effectively; they should be able to concisely express both their ideas as well as complex technical concepts.
Please answer the following questions in as much detail as you feel comfortable with. The questions are purposefully open-ended, and we hope you take the opportunity to show us your familiarity with various technologies, tools, and techniques. Limit each answer to half a page if possible; walls of text are not required, and you'll have a chance to discuss your answers in further detail during a phone interview if we move forward in the process. Finally, feel free to use google, man pages and other resources if you'd like.
Knowing what you know about ******, how would you design the high level infrastructure for ******.com? What sequence of steps would happen when loading http://www.******.com in a browser? Don't worry about describing the specific libraries and services that handle each step.
Describe the common components of web application frameworks. What purpose does each component serve? What is the benefit of separating each component from the others?
Given the following table schema, indexes, and query plan, explain how the query is executed and what you would do to improve the performance.
mysql> describe ci_statuses;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| state | varchar(255) | NO | | unknown | |
| sha | varchar(255) | NO | MUL | NULL | |
| repository_id | int(11) | NO | MUL | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| pull_request_id | int(11) | YES | MUL | NULL | |
| context | varchar(255) | YES | | default | |
+-----------------+--------------+------+-----+---------+----------------+
indexes:
+----------------------------+------------------------------+--------+
| Index_name | Columns | Unique |
+----------------------------+------------------------------+--------+
| PRIMARY | id | 1 |
| sha | sha | 0 |
| pull_request_id_created_at | pull_request_id, created_at | 0 |
| repository_id_created_at | repository_id, created_at | 0 |
+----------------------------+------------------------------+--------+
>explain SELECT r.id FROM repositories r JOIN ci_statuses s ON s.repository_id = r.id GROUP BY s.sha HAVING COUNT(s.context) > 1;
+-------------+-------+--------+--------------------------+---------+---------+-----------------+-----------+--------------------------+
| select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+--------+--------------------------+---------+---------+-----------------+-----------+--------------------------+
| SIMPLE | s | index | repository_id_created_at | sha | 767 | NULL | 122041204 | |
| SIMPLE | r | eq_ref | PRIMARY | PRIMARY | 4 | s.repository_id | 1 | Using where; Using index |
+-------------+-------+--------+--------------------------+---------+---------+-----------------+-----------+--------------------------+