Lightricks Data Engineer interview typically runs 4 rounds: HR round, online test, SQL live coding session, another HR round. It usually takes a few weeks and includes a mix of technical and behavioral checks.
$107K
Avg. Base Comp
$167K
Avg. Total Comp
4
Typical Rounds
2-4 weeks
Process Length
Our candidates report that Lightricks is less interested in flashy theory than in whether you can make clean, defensible choices with messy data. The strongest signal across the experience is query design under constraints: one prompt asked for pairwise GDP differences without duplicates, another for state-level user ratios, and another for ranking songs by plays. In each case, the interviewer seemed to care as much about why the solution was structured that way as about the final result. That means they’re watching for careful joins, correct aggregation boundaries, and whether you notice edge cases like null handling or one-row outputs.
A recurring theme is that they also want to see how you reason about evolving user behavior over time. The feature-activity problem, where “expert” status depends on the third use and affects later rows only, is a good example: this is the kind of logic that separates someone who can write SQL from someone who can model product behavior accurately. We’ve also seen a live discussion around SCD-style session logic, which suggests they value candidates who can translate ambiguous event data into a stable analytical structure. In other words, stateful thinking matters here.
The HR conversation appears to be straightforward, but the technical side rewards candidates who can explain tradeoffs clearly. One candidate explicitly noted that the interviewer probed why they chose a particular solution path, which tells us Lightricks is checking for judgment, not just syntax. If your approach is correct but brittle, or if you can’t justify how it handles real product data, that’s where interviews here tend to get interesting.
Synthetized from 1 candidates reports by our editorial team.
Had an interview recently?
Share your experience. Unlock the full guide.
Real interview reports from people who went through the Lightricks process.
1- hr round 2- online test 3 - sql live coding session 4 - another hr round the fisrt round was fine. The HR was nice, we talk about my expirience, why i want looking for a new job. online was not easy. 5 or 6 sql questions in live coding we talk about test and my solutins (why i choose my way). after that i did 1 question (45 minutes). in the end they ask question about insurance company example (what to do if company have some unprofitable clients). it was more about to check the way how you thinking (not really related to tech DE job) Questions asked: online test questions: 1 question below you can see table with gdp (in billion usd) of each country country id: 1, 2, 3, 4, 5 country name: us, canada, brazil, mexico, argentina gdp: 23315, 1988, 1609, 1273, 487 write query that shows absolute difference between each 2 countries. order results from largest difference to the smallest, without duplicates (meaning only the difference between A & B, not B & A and not A & A) note: query output should match following structure. PLease do not add columns or modify their order: country name, country name, diff 1 answer: SELECT c1.country_name AS "Country 1", c2.country_name AS "Country 2", ABS(c1.gdp - c2.gdp) AS diff FROM countries c1 JOIN countries c2 ON c1.country_id < c2.country_id ORDER BY diff DESC; 2 question: tables: users table -each row represents a user of the app songs table - each row represents song offered for streaming on the app. plays table - each row represents song that was played on the app by a user. glossary: user_id: unique id of the user email: users email address city: users city residence state: users state residence state_id: unique id for state song_id: unique id for song somg_name: name of the song artist_name: name of the aritst number_of_plays: number of times that song was played answer on the questions write query that calculates for each state, the ratio between the number of users in the state and total number of users (in all states). The query should return the name of the state , the number of users in the state, ratio ratio should be presents as a decimal number rounded to 2 decimal places. 2 answer SELECT u.state AS state_name, COUNT(u.user_id) AS number_of_users, ROUND(CAST(COUNT(u.user_id) AS DECIMAL) / (SELECT COUNT(*) FROM users), 2) AS ratio FROM users u GROUP BY u.state ORDER BY ratio DESC; second part of question 2 write query that returns for each state, the 2 songs that the largest total number of plays. The query should return the name of the state and the names of the songs. If only one song was played in a state - the query should return null for the second song answer WITH RankedSongs AS ( SELECT u.state AS state_name, s.song_name, SUM(p.number_of_plays) AS total_plays, ROW_NUMBER() OVER (PARTITION BY u.state ORDER BY SUM(p.number_of_plays) DESC) AS rank FROM users u JOIN plays p ON u.user_id = p.user_id JOIN songs s ON p.song_id = s.song_id GROUP BY u.state, s.song_name ) SELECT rs1.state_name, rs1.song_name AS top_song_1, rs2.song_name AS top_song_2 FROM RankedSongs rs1 LEFT JOIN RankedSongs rs2 ON rs1.state_name = rs2.state_name AND rs1.rank = 1 AND rs2.rank = 2 WHERE rs1.rank = 1; question 3: tbl_feature_activity table columns: uid, user, feature, action_type, action_time, platform we have table that that contains the user activity in the app. A user can either view a feature or use a feature. uid - unique id for the record user - id for the user feature - feature name action_type - user action type. either view or use action_time - timestamp of the activity platform - platform that user used. either ios or android. you can assume that a user can not have 2 activities at the exactly the same time (action time is unique per user) user becomes an "expert" after using (action type = use) the feature at least 2 times. write query that adds a column (names is_expert) to the table indicating if the activity was taken while the user was as an expert already or not. if user used the feature 2 times or less - all the records are expected to have the new column set to false. when user used the feature 3 times or more - all the following activities (either use or view activities) should be set to true. the activities until the 3rd use, should be be set to false. sort the result by user and action time (both ascending) 3 answer: WITH ActivityCounts AS ( SELECT uid, user, feature, action_type, action_time, platform, COUNT(CASE WHEN action_type = 'use' THEN 1 END) OVER (PARTITION BY user, feature ORDER BY action_time ASC) AS use_count FROM tbl_feature_activity ) SELECT uid, user, feature, action_type, action_time, platform, CASE WHEN use_count >= 3 THEN TRUE ELSE FALSE END AS is_expert FROM ActivityCounts ORDER BY user ASC, action_time ASC; in live coding question was about create scd logic when user have few rows of same session. need to make start time and time per session and group by user
Share your own interview experience to unlock all reports, or subscribe for full access.
Sourced from candidate reports and verified by our team.
Topics based on recent interview experiences.
Featured question at Lightricks
Addressing imbalanced data in machine learning through carefully prepared techniques.
| Question | |
|---|---|
| Empty Neighborhoods | |
| Employee Salaries | |
| Top Three Salaries | |
| Subscription Overlap | |
| Experiment Validity | |
| Merge Sorted Lists | |
| Download Facts | |
| Liked Pages | |
| Third Purchase | |
| Last Transaction | |
| Retailer Data Warehouse | |
| Permutation Palindrome | |
| User Experience Percentage | |
| Distance Traveled | |
| RMS Error | |
| Search Ranking | |
| The Brackets Problem | |
| Random Forest Explanation | |
| Christmas Dinner Ingredient Optimization | |
| Hurdles In Data Projects | |
| Google Maps Improvement | |
| Attribution Rules | |
| Campaign Goals | |
| Maximum Profit | |
| Marketing Channel Metrics | |
| Sort Strings | |
| Clickstream Data | |
| Sum to N | |
| Ad Comments |
Synthesized from candidate reports. Individual experiences may vary.
An initial conversation with HR focused on your background, experience, and motivation for looking for a new role. The interviewer was described as friendly and the discussion was mostly behavioral.
A challenging SQL assessment with multiple questions covering joins, aggregations, window functions, and query design. Examples included pairwise GDP differences, state-level user ratios and top songs by plays, and labeling user activity as expert or not based on usage history.
A live SQL coding session with 5-6 questions where you walked through your solutions and explained your reasoning. The discussion included why you chose certain approaches and a session-based SCD-style problem involving start and end times per user session.
A final HR-style interview that revisited your experience and included broader thinking questions. One example was an insurance-company scenario about handling unprofitable clients, used to assess problem-solving and judgment rather than deep technical knowledge.