Bot traffic share The anti-fraud team has developed a mechanism to identify bot traffic on the store's website — when a bot visits the site, the URL parameters include the substring "bot" (case-insensitive). If a user_id has been identified as a bot at least once in December, they should always be classified as a bot. Study the dataset of user visits to the site and calculate the share of bots among the total number of users in December 2024 (round to one decimal place). Input format Visit table: - event_date (date) — date of visit - user_id (int) — unique user identifier - url (string) — link accessed Data contains no missing or invalid values. Output format The query should return a table with fields: - share (float) — share of bots among the total users in December, rounded to one decimal place.
Data Engineer
There are two transactions. First, the first transaction executes a command. Then the second transaction executes a command. Afterwards, the first transaction continues. What sequence will lead to a deadlock? ```sql -- first transaction update accounts set balance = balance + 100 where id = ?; -- second transaction update accounts set balance = balance - 50 where id = ?; update accounts set balance = + 200 where id = ?; ``` [phone]
In PostgreSQL, what minimum isolation level should be set to optimize transaction performance, where: • parallel transactions can see uncommitted changes of each other; • "dirty reads" are possible. Which isolation level should be specified to achieve this? Dirty read is impossible in PostgreSQL Repeatable read Read uncommitted Read committed Serializable
You worked in the hotfix/missing-footer branch, where you made important changes. After that, you executed: git checkout develop git branch -d hotfix/missing-footer After some time, you realize you forgot to merge this branch. How do you restore it? git checkout hotfix/missing-footer But you get an error message: branch not found. How to recover work from a remote branch?
Analysis of user activity in advertising campaigns The company keeps track of events related to advertising campaigns. Two tables are provided: • campaigns — list of advertising campaigns with their IDs and names; • events — user events by campaigns with information about event type (e.g., 'click') and time. The report should include for each campaign: • Total number of events. • Number of unique users who performed events. • Time of the first and last event for the campaign. • Campaign rank by descending total number of events. Rank is a number assigned to each row based on the data order. If two or more rows have the same value, they get the same rank, but the next rank is skipped. Only campaigns with events should be included in the report; campaigns without events are not considered. The final report should be sorted by campaign rank ascending, then by campaign_name alphabetically.
If you want to change the branch that a submodule points to, what should you do? - Submodules do not support branch switching - Remove the submodule and add it again with the desired branch - Change the branch in the submodule and commit in the main repository - Run git checkout of the desired branch in the submodule and commit the changes in the main repository - Run git submodule update --branch with the new branch specified
A table structure is created as shown in the image. How to perform the query shown in the image? Which type of join should be used to fill the 'naming' column for records with type='table_aw' and 'serial_number' for type='table2'?
Why are you considering job offers now?
The feature branch contains several commits with an incorrectly committed config.yaml file, which was later fixed. As a result, the team decided to completely remove all changes to the file from history to avoid exposing confidential settings. Initial state of the branch (git log --oneline): 1 a4b5c67 (HEAD -> feature) Refactored service logic 2 d9f0a11 Fixed config.yaml typo 3 7c1d3f2 Added temporary config.yaml 4 e3a98cd Initial commit 5 After rewriting history to remove config.yaml, the output of git log --oneline became: 6 b9e7d42 (HEAD -> feature) Refactored service logic 7 41f3b60 Initial commit What operation did the command perform?
You are working on the feature/checkout-flow branch. After a rebase attempt, the commits with adding the cart and the final order button disappeared. You look at the git log: commit 8bcd3f2 Author: You Date: Fri Apr 26 16:00:00 2025 UI fixes for mobile And the git reflog output: 2f48e9a HEAD@{0}: rebase -i (squash): UI fixes for mobile 98c1dd3 HEAD@{1}: commit: Added checkout button 3adba75 HEAD@{2}: commit: Add cart logic 8bcd3f2 HEAD@{3}: checkout: moving from main to feature/checkout-flow What happened and how to restore the work? A git pull --squash --ff-only was performed, you need to reset the last commit with reset and run git pull again A git stash was performed, restore via git stash pop A git cherry-pick --squash 8bcd3f2..98c1dd3 was performed, commits were merged manually, cannot be restored A git revert 98c1dd3 was performed, you need to undo git revert via git reset An interactive rebase with squash was performed, which destroyed part of the history, — you need to do git cherry-pick 3adba75 98c1dd3
Please tell us why you are interested in this vacancy?
In your project, a submodule for documentation located in the docs/ directory is used. You learned that important updates appeared in the remote repository of this submodule (for example, on the release branch). How do you update the docs/ submodule to the latest version from the remote repository and prepare this change for commit?
Таблица notifications содержит поле status, в котором значения: 'sent', 'delivered', 'read'. Какой из запросов корректен? select * from notifications where status like '%sent%' order by created_at desc limit 5 select * from notifications where status = 'read' order by created_at desc limit 5 select * from notifications order by status desc limit 5 select * from notifications where status not in ('sent', 'delivered') order by created_at asc limit 5 select * from notifications where status in ('sent', 'delivered') order by created_at desc limit 5
In PostgreSQL, the row version header includes the xmax parameter. What is its role in transaction management? - To create a unique identifier for the row in the table - To check the visibility of the row to other transactions - To indicate the transaction number that deleted or updated the row - To lock the row from concurrent modifications by multiple transactions - To specify the maximum value that can be stored in a numeric column
You need to quickly rollback several files to the version from a previous commit without affecting other changes. How to act? git reset --hard HEAD Delete and recreate files manually git fetch and git merge git revert HEAD git checkout HEAD^ <file1> <file2>
Analyze gym attendance You work as an analyst in a chain of gyms. You have information about user visits and subscriptions they purchase. You need to analyze the effectiveness of subscription usage. Calculate for each subscription type: • the total number of users who used this type of subscription. Count only unique user_id; • the total number of visits for this subscription. Count all visits of users with this subscription; • the share of users of this subscription in percentage of the total number of all users (rounded to one decimal place). To calculate the share, use the ratio of the number of users with this subscription to the total number of unique users. Each user can have only one subscription. Sort the result by subscription type in alphabetical order. Input format Memberships table: • membership_id (int) — unique subscription identifier • user_id (int) — unique user identifier • membership_type (text) — subscription type Visits table:
You are working on the feature/checkout-flow branch. After a rebase attempt, commits adding the cart and final order button disappeared. You look at git log: commit 8bcd3f2 Author: You Date: Fri Apr 26 16:00:00 2025 UI fixes for mobile And the git reflog output: 2f48e9a HEAD@{0}: rebase -i (squash): UI fixes for mobile 98c1dd3 HEAD@{1}: commit: Added checkout button 3adba75 HEAD@{2}: commit: Add cart logic 8bcd3f2 HEAD@{3}: checkout: moving from main to feature/checkout-flow What happened and how to restore the work?
What projects have you previously worked on in Scala? Share specific tasks and achievements in this technology.
You analyze the sessions table, where status can be 'success', 'failed', 'pending', and the 'ended_at' field can be NULL if the session is not finished. You need to select all unfinished sessions except those with 'pending' status. What can be said about the correctness of the following query? select * from sessions where ended_at is null and status != 'pending';
Report for a logistics company You are an analyst for a logistics company that keeps track of warehouse operations. You need to prepare a report on the efficiency of each warehouse. For each warehouse, calculate: • total number of operations (count_operations); • total quantity of goods processed at the warehouse (sum_quantity); • average processing time per operation (avg_processing_time), considering only operations with a specified time (not NULL), rounded to the nearest whole number; • maximum and minimum number of goods processed in a single operation (max_quantity, min_quantity); • number of operations of each type ('delivery', 'shipment', 'transfer') in separate columns: supply_operations, shipment_operations, transfer_operations. Filter warehouses where total operations exceed 2 and average processing time does not exceed 60 minutes. Sort the result by warehouse ID in ascending order. Input format Table operations: • operation_id (int) — unique operation identifier • warehouse_id (int) — warehouse identifier • operation_type (text) — operation type: 'delivery', 'shipment', 'transfer'