ozkary/ai-engineering

Working use cases with AI written on Python and TypeScript

ai-engineering's Issues

Create a SQL Query to Get the Top Ten Visited Stations

As a database developer, I need to create a complex SQL query that utilizes CTEs (Common Table Expressions) and joins to extract and analyze data efficiently. This query should identify the top 10 most visited stations for each month of the year 2024. So that, I can gain insights into station usage patterns and optimize resource allocation across the network.

Data Model:

• dim_station: Contains information about stations, including station_name (primary key).
• dim_booth: Contains details about booths (not relevant for this query).
• fact_commuter: Tracks commuter activity with the following fields:
  • created_dt (datetime) - Timestamp of entry/exit
  • entries (numeric) - Number of people entering the station
  • exits (numeric) - Number of people exiting the station
  • station_id (foreign key referencing dim_station.station_id)
  • booth_id (foreign key referencing dim_booth.booth_id) (not relevant for this query)

Requirements:

• Utilize at least two CTEs to organize the query logic.
• Perform a complex join between fact_commuter, dim_station tables to link entries/exits with station names.
• Filter data for the year 2024 based on the created_dt field in the fact_commuter table.
• Calculate the total number of visitors per station per month by summing entries and exits.
• Employ window functions (e.g., RANK) to rank stations within each month based on total visitors (descending order).
• Retrieve and present the following data for each month:
  • month (extracted from created_dt)
  • station_name (from dim_station)
  • total_visitors (combined entries and exits)
  • rank

Desired Outcome:

The generated SQL code should effectively leverage CTEs and joins to achieve the stated goals. The output should be a clear table displaying the month, station name, total visitors, and rank for the top 10 most visited stations in each month of 2024.

Transform the data frame by merging the date and time field into a single column

As a data scientist, I want to generate code using the following technologies, requirements, and specifications:

Technologies:

• Python

Requirements:

• Transform a data frame by consolidating the 'date' and 'time' columns into a date time field column named 'created'.

Specifications:

• Create a function with the name 'transform_data'
• Use pandas to perform the data transformation
• Load the data from a parameter with the CSV file path
• Save the resulting data frame to disk in Parquet format
• Return true if successful or false if is not
• For coding standards, use the guidelines outlined in the PEP 8 style guide for Python.
• Create a unit test for the transform_data function to verify its correctness. The unit test should cover different scenarios and assert the expected behavior of the function.

Create a React Login Component

As a web developer, I want to create a React component with TypeScript for a login form that uses JSDoc for documentation, hooks for state management, includes a "Remember This Device" checkbox, and follows best practices for React and TypeScript development so that the code is maintainable, reusable, and understandable for myself and other developers, aligning with industry standards.

Needs:

• Component named "LoginComponent" with state management using hooks (useState)
• Input fields:
  • ID: "email" (type="email") - Required email field (as username)
  • ID: "password" (type="password") - Required password field
• Buttons:
  • ID: "loginButton" - "Login" button
  • ID: "cancelButton" - "Cancel" button
• Checkbox:
  • ID: "rememberDevice" - "Remember This Device" checkbox

Requirements:

• Page header: "Please Enter Email and Password to Login"
• Email and password fields with validation to prevent empty submissions.
• "Remember This Device" checkbox (state managed with a hook).
• Basic structure using React and TypeScript for further development.
• JSDoc format (/** */) for component documentation.

Documentation Tags:

• @file: LoginComponent.tsx
• @description: React component for user login with email and password.
• @author: [Your Name]
• @param {string} email The user's email.
• @param {string} password, The user's password.
• @returns {boolean} True if login is successful, false otherwise.

Additional Considerations:

• Placeholder for handling form submission (e.g., sending login data to backend).
• Placeholder for error handling and feedback for invalid input.
• Placeholder for UI styling using a library or CSS.

Create the code generation flow with github and openai

Description

Create the code generation flow with github and openai. The issues with the user-story label should have enough description to allow the code generation to create a snippet with the requirements that are provided on the issue.