Consultant & Software Engineering Interview Questions

Informations de contact

Prénom*

Nom*

Email*

Carrière et Objectifs

NB: À noter de remplir les trois premières questions en langue française

Quelles sont vos objectifs de carrière dans les 5 prochaines années?

Pensez-vous continuer votre carrière au Liban ou à l'étranger ? (5 lignes minimum)

Pourquoi voulez-vous travailler chez Aliston ?

Pourquoi avez-vous choisi le domaine de l’ingénierie des logiciels ?
(Centres d’intérêt, compétences ; quel a été le facteur déterminant ?)

Object Oriented Programming

Design the data model (UML or Entity-Relationship diagram) for an online book reader system

Duplicate contacts plague every CRM. Juniors often write one-off scripts to merge them.

Task : implement a CLI tool merge_contacts.py that reads a list of contacts from STDIN (newline-delimited JSON), merges duplicates, and writes the cleaned list to STDOUT.

Two contacts are duplicates if their email fields match case-insensitively OR their (first_name, last_name, company_id) triples match exactly.

Merge rules :
  • Keep the earliest created_at.
  • Keep phone if present in either record.
  • Keep the longer title string.
  • Preserve the id of the record with earliest created_at.

Requirements : Python 3.12, no third-party libs. Run with python merge_contacts.py < contacts.jsonl > cleaned.jsonl. Provide unit tests in test_merge_contacts.py. Complexity target : O(n log n) or better.

Sample input :

{"id":1,"first_name":"Ada","last_name":"Lovelace","email":"ada@acme.io","company_id":5,"phone":"+1 555-1","title":"CTO","created_at":"2025-05-01"}
{"id":2,"first_name":"Ada","last_name":"Lovelace","email":"Ada@acme.io","company_id":5,"phone":null,"title":"Chief Tech Officer","created_at":"2025-05-03"}
{"id":3,"first_name":"Alan","last_name":"Turing","email":"alan@acme.io","company_id":5,"phone":"+1 555-2","title":"Engineer","created_at":"2025-05-02"}
            

Expected output :

{"id":1,"first_name":"Ada","last_name":"Lovelace","email":"ada@acme.io","company_id":5,"phone":"+1 555-1","title":"Chief Tech Officer","created_at":"2025-05-01"}
{"id":3,"first_name":"Alan","last_name":"Turing","email":"alan@acme.io","company_id":5,"phone":"+1 555-2","title":"Engineer","created_at":"2025-05-02"}
            

Provide your solution (code and/or explanation) below :

Database Questions

Write the SQL Query that returns the Contact FirstName, Contact LastName and Company Name from the following database tables:

Write the SQL statement that changes the Company of the Contact “Sami Johns” from “org2” to “org1”

---

Database schema (référence)

Company   (id PK, name, industry, created_at)
Contact   (id PK, first_name, last_name, email, company_id FK→Company.id, created_at)
Deal      (id PK, title, stage ENUM('lead','qualified','proposal','won','lost'),
           amount DECIMAL, company_id FK→Company.id, created_at, closed_at)
Activity  (id PK, type ENUM('call','email','meeting','task'), due_date DATE,
           completed BOOLEAN, contact_id FK→Contact.id, owner_id INT, created_at)
    

Lead ageing :
Return every lead (stage = 'lead') that has been open for > 30 days and has no associated Activities in the past 14 days.
Output : deal_id, company_name, days_open (integer).

Account manager load :
For each owner_id, list the number of incomplete Activities due in the current calendar month, ordered descending.

Conversion funnel :
Compute, for the last full quarter, the conversion rates from lead → qualified → proposal → won.
Output : four percentages rounded to one decimal.

Update owner in bulk :
Re-assign all Contacts whose Company is in industry = 'Retail' and whose last Activity was completed more than 60 days ago to owner_id = 7.

Monthly revenue trend :
Show the total amount of won Deals per calendar month for the last 12 months, including months with zero.
Output : month_start, total_amount.

Stalled deals :
Which SQL snippet correctly identifies “stalled” deals — stage unchanged for 30 days and no Activities in the past 14 days ?

A. SELECT id
   FROM Deal
   WHERE stage_updated_at < CURRENT_DATE - INTERVAL '30 day'
     AND NOT EXISTS (
         SELECT 1 FROM Activity
         WHERE completed_at > CURRENT_DATE - INTERVAL '14 day');

B. SELECT d.id
   FROM Deal d
   LEFT JOIN Activity a ON a.deal_id = d.id
   WHERE d.stage_updated_at < CURRENT_DATE - INTERVAL '30 day'
   GROUP BY d.id
   HAVING MAX(a.completed_at) <= CURRENT_DATE - INTERVAL '14 day';

C. SELECT DISTINCT d.id
   FROM Deal d, Activity a
   WHERE d.id = a.deal_id
     AND d.stage_updated_at < CURRENT_DATE - INTERVAL '30 day'
     AND a.completed_at < CURRENT_DATE - INTERVAL '14 day';

D. WITH stalled AS (
       SELECT id FROM Deal
       WHERE stage_updated_at < CURRENT_DATE - INTERVAL '30 day')
   SELECT id FROM stalled;
        

-- Select the correct snippet -- A B C D

Index-design short answer (EN) :
“Query #1 is slow in production. Propose the single best index and explain briefly (≤ 40 words) why.”

Follow-up live coding (5 min) – after the test, ask the candidate to tweak their own Query #2 or explain their index choice.
Describe in two sentences why you chose a LEFT JOIN instead of EXISTS :

Problem Solving

Definition of “Quintile”: any of five equal groups into which a population can be divided according to the distribution of values of a particular variable.

Which of the following statements BEST describes why the Managing Director of Aliston has engaged Cedars Tech?
--Select-- She believes that Aliston can become better at identifying customers who stop making purchases. She believes that Aliston can become better at identifying ways to encourage customers to spend more. She believes that, by focusing on the customers of greatest value, Aliston can drive renewed growth in their business. She believes that Aliston can predict the purchasing behaviour of its customers better

Which of the following CANNOT be concluded from the information presented in Exhibit 3 regarding the sample of customers analyzed? --Select-- Purchasing by customers in their first 90 days is an indicator of their value to Aliston in their first year Quintile 1 are the customers with the lowest profit margin for Aliston The average customer makes between 2 and 3 transactions in the first 90 days since signing up The average one-year customer value is $7.90

What is the correct ranking of the five quintiles in Exhibit 3 according to the average transaction revenue, from lowest to highest? --Select-- 1, 2, 4, 3, 5 1, 2, 3, 4, 5 1, 3, 2, 4, 5 1, 3, 2, 5, 4

If Aliston had driven higher purchasing from the new customers in Exhibit 3 so that the one year value of customers in Quintiles 1 thru 4 were each to increase to the next highest quintile, how much greater would Aliston’s total one year customer value have been?
--Select-- $6.5 million $2.5 million $650,000 $250,000

When comparing the pilot group to the control group, which of the following CAN be determined from the information provided in Exhibit 4 for customers who purchased Electronics in their first transaction?
--Select-- Percentage of total lift in number of transactions represented by each two week time segment Two-week time segment which had the greatest difference in number of transactions Difference in number of customers making transactions Percentage difference in average number of transactions per customer

If 10% of customers in the control group made transactions during the pilot, how many of the customers in the pilot group made transactions during the pilot?
--Select-- 11,700 10,170 5,850 5,085

Which of the following CANNOT be concluded from the information presented in Exhibit 4?
--Select-- Among customers whose first transaction was in ‘Other’, the pilot had greatest impact among those whose first transaction was more recent The pilot had greatest impact among customers whose first transaction was in Electronics The pilot had a positive impact on the number of transactions per customer for customers who made their first transaction 4-6 weeks before emails were sent The pilot had no overall impact on the likelihood of making further transactions among customers whose first purchase was in Clothing/ Apparel

Which of the following data from the pilot period would be LEAST useful to the team in further investigating the impact of the pilot on Aliston’s business?
--Select-- Breakdown of two-week time periods into weekly time periods Breakdown of data in ‘Other’ category to further levels of detail Percentage of pilot group emails that were not delivered and bounced back Average transaction value for pilot group and control group customers

When designing the relationship between Contact and Company in a CRM, which option best balances data integrity with flexibility for future B2B + B2C use-cases ?
-- Select -- A. One-to-many (Company owns many Contacts; company_id in Contact is NOT NULL). B. Many-to-many via a junction table (ContactCompany), plus keep company_id in Contact for the primary employer. C. Many-to-many only; remove company_id from Contact. D. One-to-one; store company details directly on the Contact table.
Skill signal : ability to reason about freelancers, job-switching, multiple affiliations and pick a pragmatic hybrid design.

Deal     (id, stage, stage_updated_at, company_id, created_at)
Activity (id, deal_id, completed_at)
    

Communication

Cite examples of accomplishments where you have been an excellent communicator. (in English)

Within 60 seconds, record a selfie-video (phone or webcam) answering both bullets :
  • Which single question number did you find the most challenging, and why?
  • One concrete suggestion that would have made this test more enjoyable.

Keep it spontaneous — no slides or screen share.

Name the file reflection_.mp4 and upload it alongside your answer sheet.

If you prefer not to appear on video, hand-write the answers on paper, add a quick sketch of an icon representing your feeling, and upload a phone photo instead.