Category: ROCKEN Documentation
-
Report SQLs
Here should be a list of SQLs for reports requested by clients. So they can be reused.
Application stats
-
Count of applicant events by month
SELECT EXTRACT(YEAR FROM to_timestamp(arh.created_at)) AS year, EXTRACT(MONTH FROM to_timestamp(arh.created_at)) AS month, COUNT(DISTINCT case when arh.event = 1 then c.id end) as unfinished, COUNT(DISTINCT case when arh.new_value = '2' then c.id end) as new, COUNT(DISTINCT case when (arh.new_value = '4' OR arh.new_value = '8') AND (arh.prev_value = '2' OR arh.prev_event = 1) then c.id end) as rejection, COUNT(DISTINCT case when arh.new_value = '3' AND (arh.prev_value = '2' OR arh.prev_event = 1) then c.id end) as in_progress, COUNT(DISTINCT case when arh.new_value = '7' AND arh.prev_value = '3' then c.id end) as interview, COUNT(DISTINCT case when arh.new_value = '5' AND arh.prev_value = '7' then c.id end) as active, COUNT(DISTINCT case when (arh.new_value = '4' OR arh.new_value = '8') AND arh.prev_value = '7' then c.id end) as rejection_after_interview, COUNT(DISTINCT case when arh.new_value = '6' AND (arh.prev_event = 1 OR arh.prev_value = '2' OR arh.prev_value = '3' OR arh.prev_value = '7') then c.id end) as offline FROM ( SELECT c.id, arh.created_at, arh.event, arh.new_value, LAG(arh.new_value) OVER (PARTITION BY arh.row_id ORDER BY arh.created_at) prev_value, LAG(arh.event) OVER (PARTITION BY arh.row_id ORDER BY arh.created_at) prev_event FROM arhistory AS arh LEFT JOIN candidates AS c ON arh.row_id = c.id WHERE arh.table_name = 'candidates' AND c.date_add > '2022-10-01 00:00:00' AND c.id IS NOT NULL AND (arh.field_name = 'status_id' OR arh.field_name IS NULL) ) AS arh LEFT JOIN candidates AS c ON c.id = arh.id GROUP BY year, month ORDER BY year, month;
2. Average status transition time by month
SELECT EXTRACT(YEAR FROM c.date_add) AS year, EXTRACT(MONTH FROM c.date_add) AS month, AVG((GREATEST(rejectionDate, rejectionDeletionDate) - unfinishedDate)/60/60/24) AS avg_days_to_rejection, AVG((inProgressDate - unfinishedDate)/60/60/24) AS avg_days_to_inprogress, AVG((interviewDate - inProgressDate)/60/60/24) AS avg_days_from_inprogress_to_interview, AVG((activeDate - interviewDate)/60/60/24) AS avg_days_from_interview_to_active, AVG((offlineDate - unfinishedDate)/60/60/24) AS avg_days_from_unfinished_to_offline FROM ( SELECT DISTINCT ON (unfinishedDate, newDate, inProgressDate, rejectionDate, activeDate, offlineDate, interviewDate, rejectionDeletionDate) c.id, MAX(arh.created_at) filter (where arh.event = 1) as unfinishedDate, MAX(arh.created_at) filter (where arh.new_value = '2') as newDate, MAX(arh.created_at) filter (where arh.new_value = '3') as inProgressDate, MAX(arh.created_at) filter (where arh.new_value = '4') as rejectionDate, MAX(arh.created_at) filter (where arh.new_value = '5') as activeDate, MAX(arh.created_at) filter (where arh.new_value = '6') as offlineDate, MAX(arh.created_at) filter (where arh.new_value = '7') as interviewDate, MAX(arh.created_at) filter (where arh.new_value = '8') as rejectionDeletionDate FROM arhistory AS arh LEFT JOIN candidates AS c ON arh.row_id = c.id WHERE arh.table_name = 'candidates' AND c.date_add > '2022-10-01 00:00:00' AND c.id IS NOT NULL AND (arh.field_name = 'status_id' OR arh.field_name IS NULL) GROUP BY c.id ORDER BY unfinishedDate, newDate, inProgressDate, rejectionDate, activeDate, offlineDate, interviewDate, rejectionDeletionDate) AS arh LEFT JOIN candidates AS c ON c.id = arh.id GROUP BY year, month ORDER BY year, month;
-
-
Tasks status – Sprint 38
Dev
Task
Estimated time
Spent time
Comment
Result
3D
2h
Looks like in progress from 9.03, but status is Open?
NEXT SPRINT
1D 4h
1D 6h
REVIEW
2D
1D 5h
Real remaining time – ?
NEXT SPRINT
6h
4h
In Review (Lack of Anton’s approve)
REVIEW
2D
3D
Return from Review by Dima 7.03
REVIEW
–
2,5h
Started, and assigned to Anton
–
–
2h
NOT PLANNEDStarted in Sprint 34, returned because Review
Total Spent – 2W
Planned – 1WREVIEW
–
2h
NOT PLANNEDStarted in Sprint 36, returned because Review
Total Spent – 3W
Planned – 3DDONE
–
1.5h
NOT PLANNEDStarted in Sprint 36, returned because Review
Total Spent – 1W
Planned – 3DDONE
–
1.5h
NOT PLANNEDStarted in Sprint 35, returned because Review
Total Spent – 3D
Planned – 1.5DREVIEW
–
1h
NOT PLANNEDStarted in Sprint 36, returned because Review
Total Spent – 1.5D
Planned – 1DREVIEW
Code Reviews
–
2h
Total (planned): 9.5D
±Spent: 8.5D
1.5D
7h
Task from Sprint 36
Total Spent – 3D
Planned – 1.5DDONE
1D
1D 3h
Was Returned from Review by Dima
DONE
2D
2D 2h
Was Returned from Review by Dima
NEXT SPRINT
7D
2D 3h
Potentially will be continued in the Sprint 39
NEXT SPRINT
1,5h
1h
Added to the Sprint
DONE
Linux set up
1D
1D
DONE
Code Reviews
–
2.5h
Total (planned): 12.5D
±Spent: 8.5D
Bug hunter
4h
1D
Task from previous Sprints
DONE
4D
–
Can be transferred to the Sprint 39
NEXT SPRINT
4D
–
Can be transferred to the Sprint 39
NEXT SPRINT
1h
0.5h
DONE
4h
–
NEXT SPRINT
–
–
NEXT SPRINT
–
–
NEXT SPRINT
–
1D
DONE
1D
3D
High priority
OK Spent time, because only research was estimatedNEXT SPRINT
–
1.5D
NOT PLANNED
QA
–
7h
NOT PLANNED
NEXT SPRINT
4h
4h
NOT PLANNED
DONE
–
1h
NOT PLANNEDTask from Sprint 34, Returned from Review
Total Spent – 1W
Planned – 4DDONE
Code Reviews
2.5h
Total (planned): 10D
±Spent: 8.5D
4h
5D
Task from previous Sprint
DONE
4h
–
Task from previous Sprint
NEXT SPRINT
2h
0.5h
DONE
5D
2h
DONE
2.5D
–
Changed priorities for 1788
NEXT SPRINT
2.5D
–
Changed priorities for 1788
NEXT SPRINT
2D
3.5D
New task added to the Sprint
?
Code Reviews
12h
Total (planned): 12D
±Spent: 10D
5D
4D
REVIEW
2D
5h
Task from previous Sprint
Was started in Sprint 37
Spent time all – 1.5DDONE
1.5D
1D 1h
Task from previous Sprint
DONE
2D
2D
DONE
–
40m
NOT PLANNEDTask from Sprint 34, Returned from Review few times
Total Spent – 3D
Planned – 2D 4hDONE
Code Reviews
1h
Total (planned): 10.5D
±Spent: 8D
1.5D
7h
Was Returned from Review by Dima
REVIEW
3D
7h
REVIEW
1.5D
–
Why the Spent time is not added?
DONE
2D
10h
High priority
DONE
2D
2D
Was Returned from Review by Dima
REVIEW
2D
13h
Was Returned from Review by Dima
REVIEW
–
9h
NOT PLANNED From previous Sprint
Total Spent – 4.5D
Planned – 3DREVIEW
1.5D
3.5h
NOT PLANNEDStarted 09.03
NEXT SPRINT
Code Reviews
3h
Total (planned): 12D
±Spent: 8.5D
Pay attention: -
Rocken Sprint 38 – Planning & Goals
Sprint planning checklist
Preparation
Meeting
Follow up
- Sprint planning call with team
- Close & clean previous sprint
- Discuss planned tasks
- Add estimates (poker estimating)
- Make sure all estimations are added to the tasks
- Demo after each sprint
Sprint team membersName
Role
Main Goal
Planned Capacity
Note
Project Manager
–
–
Product Manager
–
–
TeamLead, BE/FE Dev
New Architecture
12 D
BE Dev
New Architecture
11.5 D
BE Dev
New Architecture
9.5 D
Bughunter/BE Dev
Support, New Architecture
10D + Support
FE Dev
New Architecture
8.5D
FE Dev
New Architecture
11.5 D
FE/BE dev
SEO tasks
–
Designer
CRM, Task manager
–
QA
New Architecture, Support QA
–
Roman
DevOps
Deploy BI board, CRM, Talent; VPN
–
Freelancer
Dev
3D animation
–
Sprint GoalsDetails
Start date
End date
Sprint theme
New Architecture, Support cases, Security measures
New Architecture:
Back-end:
-
API Profile: Skills CRUD, Change Profession
-
API Vacancy: Address
-
API: Change user role
-
Modules: Notification, Publication, Process (Actions, History of process changes), Chat
Front-end:
-
Talent Dark theme
-
Forms: Interview, Account info, Languages
-
Full/short profile page
-
Sessions page
-
Base table
-
Components: Saved filters, Date picker
General:
-
Frontend refactoring
-
Slug with save old links
-
Access and OA standard
-
Front + back Date-time standard
Design:
-
CRM + general design tasks
-
Task manager
DevOps:
-
CI/CD Deploy BI board, CRM, Talent
-
VPN live
-
Rocken Sprint 37 – Planning & Goals
Sprint planning checklist
Preparation
Meeting
Follow up
- Sprint planning call with team
- Close & clean previous sprint
- Discuss planned tasks
- Add estimates (poker estimating)
- Make sure all estimations are added to the tasks
- Check if there are tasks for Anna – RJ/SEO
- Demo after each sprint
Sprint team membersName
Role
Main Goal
Planned Capacity
Note
Project Manager
–
–
Product Manager
–
–
TeamLead, BE/FE Dev
New Architecture
10 D
BE Dev
New Architecture
10 D
Bughunter, BE Dev
Support, New Architecture
4 D + Support
BE Dev
New Architecture
7D
9-14.02 Sick leave
Continuing Process moduleFE Dev
New Architecture
9D
FE Dev
New Architecture
9.5 D
FE/BE dev
SEO tasks (no tasks at the beginning of the Sprint)
–
Designer
CRM, BI board updates
–
QA
New Architecture, Support, RJ QA
–
Roman
DevOps
Deploy BI board, CRM, Talent; VPN
–
Freelancer
Dev
3D animation
–
Sprint GoalsDetails
Start date
End date
Sprint theme
New Architecture, Support cases, Security measures
New Architecture:
Back-end:
-
API Profile: Languages, Skills CRUD, Change Profession
-
API Vacancy: Languages, Address
-
API: Get attachments, User & profile attach avatar, Phone numbers (for User & Profile + test SMS module)
-
Modules: Administrative data, Process (first iteration), Social link with connection trait
Front-end:
-
Talent Dark theme
-
Profile header
-
Job: Job page, My request, Search and filter panel
-
Forms: About Profile, Interview
-
Components: Saved filters, Date picker
General:
-
Frontend refactoring
-
Access and OA standard
-
Front + back Date-time standard
Design:
-
CRM + general design tasks
-
BI board updates
DevOps:
-
CI/CD Deploy BI board, CRM, Talent
-
Transfer CRM database to DigitalOcean
-
VPN solution
-
Rocken Sprint 36 – Planning & Goals
Sprint planning checklist
Preparation
Meeting
Follow up
- Sprint planning call with team
- Close & clean previous sprint
- Discuss planned tasks
- Add estimates (poker estimating)
- Make sure all estimations are added to the tasks
- Make a report of workload of the team
- Test S3 uploading files when Ivan comes back w/ Dasha
- Discuss tasks “Ready for Testing” w/Ivan
Sprint team membersName
Role
Main Goal
Planned Capacity
Note
Project Manager
–
–
Product Manager
–
–
TeamLead, BE/FE Dev
New Architecture
10 D
Bug Hunter, BE Dev
Support, New Architecture
10.5 D
BE Dev
New Architecture
11 D
BE Dev
New Architecture
12 D
FE Dev
New Architecture
9.5 D
FE Dev
New Architecture
10 D
FE/BE dev
SEO tasks (no tasks at the beginning of the Sprint)
–
Designer
CRM
–
QA
New Architecture, Support, RJ QA
–
23/01 – 03/02 Vacation
Roman
DevOps
Deploy BI board, CRM, Talent; VPN
–
Simon + freelancer
DevOps
3D animation
–
Add request for HR to find freelancer with such experience
Sprint GoalsDetails
Start date
End date
Sprint theme
New Architecture, Support cases, Security measures
New Architecture:
Back-end:
-
API Profile: Languages, Skills, Description CRUD, Profile – Experience,
-
API Vacancy: Industry, Languages, Address, Namage search info form
-
Modules: Administrative data, Process (first iteration)
-
User email verification
Front-end:
-
Profile Skill page
-
Forms: Change algorithm, Contact, General info, Education list/form, Experience list/form
-
Components: Process, Date time standard
General:
-
Edit layout structure
-
Localize, http factory solution
Security:
-
Close RT Admin access with 2FA
-
Close Production CRM / Services connections
Design:
-
CRM + general design tasks
DevOps:
-
CI/CD Deploy BI board, CRM, Talent
-
VPN solution
-
ROCKEN Update (December – January)
Headlines
Highlights
-
Back-end is heading to the support stage; most of what remains is copy paste, bugs and edits from the front-end
Lowlights
-
Front-end lags behind the backend, due to the lack of deployment, there are potentially many bugs, so the support stage is planned to be longer than in the backend
Risks
-
Back-end: Matching, Smart Search
-
Deploy: There is a trouble with specialists, if Dima would take care of it by himself we can not guarantee the quality, and progress on the front and back will be halted
-
Team interaction while working with global components (components that can be used in other systems), plan to standardize everything clearly in the next sprint
Plans
-
Ideally, in about a month we will be able to recode resources from the back to the front (full stacks)
-
In 4-5 sprints the Talent will be ready, and we will move on to CRM; CRM development should be faster as most components will be transferred from Talent
Highlights progress & overview
Back-end
Completed:
On Review:
-
Goals and budgets
-
Jobs
-
Languages
-
Education
-
Nationalities
-
Industries
-
Schools
-
Editors *
*functionality that records changes to any entity, you can track the entire history of any changes
-
Companies
-
Publications
-
APIs for interaction:
— Profile (Candidate) – Education,
— Profile – Interview
— Profile – Base info
— Profile – Description
— Profile – Base data -
API for sessions (session with a device, logout from devices, etc.)
Plans:
-
Work on Experience module (actually a clone of Education + Company)
-
Basically the same API for Vacancies, Profiles + skills, Languages, etc.,
-
Invitations to interviews
-
Apply for vacancies
-
Risk-free, but takes additional time:
— Think through and standardize access to methods (for example, who can and cannot edit a profile, in which cases)
— Various reports for BI board and CRM
Risks:
-
Research and implementation of Matching
-
A little research (we know the approaches, we just need to choose them) and implementation of a Smart Search (±2weeks)
Problems:
Due to electricity issues and problems with the old system, the review is delayed, tests and some errors are multiplied by modules, it is necessary to spend time on their refactoring in many places.
Front-end
Completed:
On Review:
-
All the basic components
-
Talent and BI board layout
-
Authorization
-
All components of the BI board are ready, except for the Editor mode
-
Ready (but not connected to the API):
— Language profile
— Experience profile
— Education profile forms -
The profile page is almost ready (except for the header with the profile pic)
* Something works on the ready-made API, something on data locking, but there should be no problems using the real API
-
Profile-Skills form
-
Work on invitations to interviews (this is a little out of the logic of the tasks, but in the work to set the right tasks for the backend)
-
Components for the page or preview (in the modal window) of vacancies
-
Calendar of events
Plans:
-
After approval of the BI board’s Editor mode – Artem will develop it and gather the BI board as a project (the first version will be ready depending on the complexity of the Editor mode)
-
Interview
-
We finalize the Profile (Preview, Editing) → moving to the pages with Jobs (Applications, Notifications, and Vacancies) → Dark theme +- 2 days and tests
-
Next, we switch to CRM (somewhere here we will be able to attract a team from the backend)
Problems:
Deploy – the problem with resources, but already working on it and hopefully would have it done by 10/02
-
-
Rocken Sprint 35 – Planning & Goals
Sprint planning checklist
Preparation
Meeting
Follow up
- Sprint planning call with team
- Close previous sprint
- Discuss planned tasks
- Add estimates (poker estimating)
- Discuss Alternative tracking tool
- Make sure all estimations are added to the tasks
- Ping Anna to add estimates to her tasks
- Present Tempo
Sprint team membersName
Role
Main Goal
Planned Capacity
Note
Project Manager
–
Product Manager
–
TeamLead, BE/FE Dev
New Architecture
BE Dev
New Architecture
Bug Hunter, BE Dev
Support, New Architecture
BE Dev
New Architecture
FE Dev
New Architecture
FE Dev
New Architecture
BE/FE Dev
SEO, RJ tasks
Designer
BI board
QA
New Architecture, Support, RJ QA
23/01 – 03/02 Vacation
Roman
DevOps
Deploy BI board, CRM, Talent
Yurii
DevOps
3D animation
Sprint GoalsDetails
Start date
End date
Sprint theme
New Architecture, Support cases, RJ fixes
New Architecture:
Back-end:
-
API Profile: Languages, Skills, Description, Education
-
Modules: Publication, Vacancy, Administrative, Industry, Nationality, Company (first iteration)
-
Editor/creator trait
Front-end:
-
Profile API: Skills, Language, Experience, Education
-
Login page
-
Forms: Contact info, General info
-
Components: Distance input, Notification list, Base pagination, Date time standard
General:
-
Edit layout structure
-
Localize, http factory solution
-
Talent, CRM, BI SSR strategy
SEO:
-
Pre-render errors in Slack bot
-
Vacancy region sitemap split
-
Search settings fixes
Design:
-
BI board: Goals, Budgets + general design tasks
DevOps:
-
CI/CD Deploy BI board, CRM, Talent
-
Bug report template
Summary
Rule
Example
Title should be self-descriptive ("What?" "How behaves?" "While what conditions?").
[Vacancies]. Error page is displayed after visiting the Vacancy page and changing its status.
Bug specific should be stated on first place (ex.: reproducible only on some device type, intermittent issue, environment specific, any other unique attribute).
[Tablet] [Mobile] [RT]. Profile page is not adapting to the new layout after rotating the device.
[Intermittent] [PROD] [Companies]. Updates are not displayed on the company preview page after clicking the “Vorchau“ button.Component name should be stated at the beginning of the title.
[Applicants]. User is not shown in the Applicants tab after applying on the publication.
Avoid using not exact phrases such as "working not appropriately", "not proper way","not per design". Try to be as specific as possible.
[PaaS]. Report is downloaded in a not proper format.
Description
-
Steps should be as specific as possible;
-
Examples of pages, profiles, vacancies, etc. that could be used for easier bug reproducing should be provided in any suitable form (URL, ID, etc.);
-
Actual and Expected results should be provided with appropriate screenshots, whenever applicable;
-
During bug creation, separate critical/major/minor bugs and create a separate bug for each issue;
You can combine the bugs if they have the same priority. But if the priority is different – please create 2 or more bugs. -
If during retesting the bug, the issues described initially are fixed, close the bug, and for all new issues that appeared after the fix and weren’t described in the bug initially create a NEW bug.
Bug template
Filed
What to fill
Filed
What to fill
Project
ROCKEN-TALENT (RT)
Issue type
Bug
Summary
A brief one-line summary of the issue.
Summary field name should be filled according template:
[Area name] <Problem short and concrete description>Component
<Component’s name> select from the dropdown
Description
Description field should be filled as in following template:
Preconditions:
REPLACE the TEXT with needed configuration, if applicable.
Steps to reproduce:
-
Step_1.
-
Step_2.
-
Step_3.
Actual result:
Clear description of what actually happened.
See attached screenshot for more details.
<Screenshot>
Expected result:
Clear description of what should have been happened.
See attached design for more details.
<Screenshot> / <link to the design>
Additional information:
REPLACE the TEXT with Additional information, if applicable.
Priority
The degree of importance for the business to resolve the defect. It is driven
by business value and indicates how soon the defect should be fixed.Labels
Choose “Rocken“ from the drop down. The label indicates the bug is related to some component. For each project, the list of labels is specific.
Environment
one of the (STG, PROD) + version (for NA https://api.rocken-talent.cheitgroup.com/api/v1/version)
Attachment
If you can supplement your bug report with a picture that shows the
problem, or a score that helps others reproduce, fix and verify the problem
quickly, attach these files to the bug report. The attached files can be as
follows: pictures, video-recording, other files types, if needed.Linked Issues
<link to the related issue>
Assignee
The person whom the bug is assigned to (backend issues – BE/Dev Lead,
Frontend issues – FE Lead)Epic Link
Contains a link to Epic in accordance with overall structure of epics on the
project.Sprint
optional
QA Scope
<testing scope>
-
