Analysis of fitness club attendance You work as an analyst in a chain of fitness clubs. You have information about user visits and the memberships they purchase. It is necessary to analyze the effectiveness of membership usage. Calculate for each type of membership: • the total number of users who used this type of membership. Consider only unique user_id; • the total number of visits for this membership. Consider all visits of users with this membership; • the share of users of this membership 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 membership to the total number of all unique users. Each user can have only one membership. Sort the result by membership type in alphabetical order. Input format Memberships table: • membership_id (int) — unique membership identifier • user_id (int) — unique user identifier • membership_type (text) — type of membership Visits table: • visit_id (int) — unique visit identifier • user_id (int) — user identifier • visit_date (timestamp) — date and time of visit Data does not contain missing or incorrect values. Output format The query should return a table with fields in this order: • membership_type (text) — type of membership • users_count (int) — number of unique users with this type of membership • total_visits (int) — total number of visits of users with this membership • user_share (numeric) — share of users of this membership in percentage of the total number (rounded to 1 decimal place) The result is sorted by membership type in alphabetical order.
Data Engineer
Tell me, are you familiar with the Domain Driven Design (DDD) methodology? If yes, share examples of its application in your projects.
Which expression in place of the missing [...] will automatically create an index? There is a horizontal code scroll on the mobile platform create table some_table( col_name [...] ); unique references other_table(col_name) not null serial integer check (col_name > 0)
How to remove a submodule and its related files from a project? git submodule remove <path-to-submodule> git rm --cached <path-to-submodule>; remove section from .gitmodules; git commit git clean --submodules <path> git submodule delete <path> git remove submodule <path>
A regular B-tree index on id is created in the records(id) table. Why will the next query not use the index? select * from records where id % 2 = 0 - GIN type index is needed for id - Indexes do not work with expressions in WHERE - % is a comparison operation, not a filter - limit and offset are mandatory for index optimization - The query accesses a numeric field, not a string field
Report for a logistics company You are an analyst at a logistics company that keeps track of warehouse operations. You need to compile 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 of an 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 number of operations is more than 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) — type of operation: 'delivery', 'shipment', 'transfer' • quantity (int) — number of goods units in the operation • operation_date (timestamp) — date and time of the operation • processing_time (int) — processing time of the operation The processing_time column may contain nulls. Output format The query should return a table with fields in the following order: • warehouse_id (int) — warehouse identifier • count_operations (int) — total number of operations at the warehouse • sum_quantity (int) — total quantity of goods processed at the warehouse • avg_processing_time (numeric) — average processing time of operations (in minutes), considering only non-NULL times, rounded to the nearest whole number • max_quantity (int) — maximum number of goods processed in a single operation • min_quantity (int) — minimum number of goods processed in a single operation • supply_operations (int) — number of 'delivery' operations • shipment_operations (int) — number of 'shipment' operations • transfer_operations (int) — number of 'transfer' operations.
During the process of working with git bisect, you encountered a commit that cannot be tested due to the absence of the necessary environment. What should you do in this situation? - Repeat the command git bisect start with different hashes - Skip this commit with the command git bisect skip - Reset bisect with the command git bisect reset - Mark the commit as good with the command git bisect good - Mark the commit as bad with the command git bisect bad
A special sequence called even_sequence was created, which generates only even numbers. What should be substituted in place of [...], so that if the value of even_column was not specified during insertion, the value is taken from even_sequence? create table some_table( even_column [...] ); integer computed as nextval('even_sequence') integer generated always as identity (start with 2 increment by 2) integer default nextval('even_sequence') integer unique default nextval('even_sequence') integer generated by even_sequence’
Explain how Git LFS works technically and what advantages it offers compared to standard Git when working with large files.
Do you have experience working with the Langchain library? What tasks have you solved using it?
What would you like to do in our team?
Как Git Flow рекомендует оформлять новый релиз приложения? - Созданием новой issue-ветки - Созданием hotfix-ветки от master - Созданием отдельной release-ветки от develop - Прямым коммитом в ветку master - Слиянием ветки master напрямую в develop
You are building a view that shows all users and, if available, their latest orders. Tables: users(id, name) and orders(id, user_id, created_at). What type of join should be used to include users without orders? CROSS JOIN RIGHT JOIN INNER JOIN FULL JOIN LEFT JOIN
You are working in a team that uses the extended Git Flow methodology with additional branch types. Your project has the following branch structure: - main (stable releases) - develop (current development) - release/* (release preparations) - hotfix/* (urgent fixes) - feature/* (new features) - experimental/* (experimental features) - refactor/* (code refactoring) One of your colleagues performed the following actions: - Created a branch refactor/db-constraints from develop - Made 15 small commits in it, each refactoring a separate part of the database code - Merged the refactor/db-constraints branch into develop using: git merge --squash refactor/db-constraints - Created a new commit in develop with a detailed description of all changes - Did not delete the refactor/db-constraints branch after merging Which of the following conclusions reflects the consequences of such actions in terms of extended Git Flow and change history management?
You found that there are commits containing critically important confidential data in the main repository's history. These data need to be completely removed from the entire repository history. Evaluate how correct and safe it would be to use the following strategy: create a new commit that removes the confidential data from the current version of files, and push it to main. - Correct but not optimal. Better to use git revert to undo commits - Conditionally correct. This is a temporary solution until a more radical means of data removal is found - Incorrect and unsafe. Data will be removed from the current version but will remain accessible in the repository history - Incorrect. Such a commit may lead to new conflicts when merging with other branches - Correct and safe. This method guarantees that data will be removed and will not appear in the repository anymore
You are working on a new feature in the dev branch. Suddenly, there is a need to urgently switch to the main branch to quickly fix a typo in the README.md file. You have some uncommitted changes in src/feature.js (unstaged) and styles/main.css (staged). You want to temporarily save all these changes to return to them later in the dev branch.
Sales analysis by product categories in a retail store You work as an analyst in a retail store. Your task is to generate a sales report by product categories with calculations: • total units sold in the category; • total revenue considering discounts, where discount is applied as unit_price × units_sold × (1 − discount/100). If no discount (NULL), consider it as 0%; • average units per sale, rounded to two decimal places; • share of sales without discount, calculated as the number of sales with NULL or 0% discount divided by total sales in the category, rounded to three decimal places. Sort the result first by descending total_revenue, then by ascending average units per sale, and finally by category name alphabetically. Input format Table sales: • sale_id (int) — unique sale identifier • product_id (int) • category (text) • sale_date (timestamp) • units_sold (int) • unit_price (numeric) • discount (numeric, nullable) Output format Return a table with columns in this order: • category (text) • total_units_sold (int) • total_revenue (numeric) • avg_units_per_sale (numeric) • no_discount_share (numeric) Sort the results as specified.
[name] corrected the errors and style in the text: "Hello! I am [name], you previously asked to switch to Telegram" — to make it informal, but respectful.
Do you have experience working with the Gin web framework? Please tell more about the tasks you solved with its help.
In a project with microservices architecture, a critical security vulnerability was discovered, introduced by commit abc123 two months ago. This commit affects the shared authentication library used across all services. The situation is complicated by the following factors: 1. More than 200 commits have been made to the main branch after the problematic commit. 2. Five release branches have been created from the main branch, which also contain this commit. 3. Several development teams have created feature branches from different points in history. 4. Some subsequent commits in the main branch partially fix the issue but not completely. 5. All affected branches have already been deployed in various environments (testing, pre-production, production). Which strategy would be most effective for removing the vulnerability from all branches with minimal risk of disrupting the project? - Create a series of revert commits for the problematic commit and all commits that partially fixed it, then create a new commit with a complete fix. - Create a hotfix branch from a point before the problematic commit, make fixes, and merge this branch into all affected branches. - Create a single fix commit using git revert abc123 in the main branch and then cherry-pick this commit into all release branches. - Use git bisect to precisely identify the problematic code, create a patch, and apply it to all branches using git am. - Use git rebase -i to edit the problematic commit in each branch, followed by force-pushing the changes.