Employees Who Joined More Than a Week Ago
Updated
Problem statement
Get the list of employees who joined before a week.
For this exercise, assume that the tables below are supplied. An employee qualifies when join_date is strictly earlier than reference_date minus seven calendar days. A join date exactly on that cutoff does not qualify.
Input tables
employees(id, name, join_date) contains employee IDs, names, and calendar join dates. snapshot(reference_date) contains exactly one supplied reference date. Use this date rather than the current system date.
Required result
Return columns id, name, and join_date for every qualifying employee, ordered by id ascending.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
employees
| Column | Type | Nullable | Description |
|---|---|---|---|
| idPK | Integer | No | — |
| name | Text | No | — |
| join_date | Date | No | — |
snapshot
| Column | Type | Nullable | Description |
|---|---|---|---|
| reference_date | Date | No | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| id | Integer | No | — |
| name | Text | No | — |
| join_date | Date | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
employeescontains zero to 40 rows. Employee IDs are distinct positive integers at most 2,147,483,647.- All values are non-null. Names may repeat and contain 1 to 100 printable ASCII characters.
snapshotcontains exactly one row.- Dates are valid calendar dates from 1900-01-08 through 2100-12-31. An employee may have a future join date; such a row does not qualify.
- Subtract seven calendar days, then use a strict earlier-than comparison.
More Newgen Software interview questions
Similar questions at other companies
- Second Highest SalaryPaytm · PHONE SCREENPaytm interview questions
- Employees Who Earn More Than Their ManagersNykaa · PHONE SCREENNykaa interview questions
- Departments Without EmployeesDell Technologies · PHONE SCREENDell Technologies interview questions
- Second-Highest Distinct SalaryChubb · PHONE SCREENChubb interview questions
- Swap Two Gender ValuesInfosys · PHONE SCREENInfosys interview questions
- Find Rows Common to Two TablesSalesforce · PHONE SCREENSalesforce interview questions
- Count Records Present in A but Not BCapital One · PHONE SCREENCapital One interview questions
- Customer Order Count Distribution by CountryApple · PHONE SCREENApple interview questions
- Employees Earning More Than Their ManagerMygate · PHONE SCREENMygate interview questions
- Classify Tree Nodes with SQLOracle · PHONE SCREENOracle interview questions