Data Science and ML Coding Practice
0 of 22 passed
Continue practiceSQL & Databases
- Select customer columnsBasic SQLDifficulty: beginnerReturn customer_id and name from every row in customers, in that column order. Keep duplicate names. Row order does not matter.
- Filter paid ordersBasic SQLDifficulty: beginnerReturn order_id and amount for orders whose status is 'paid' and amount is at least 100. Include exactly 100; exclude NULL amounts. Row order does not matter.
- Find the three most expensive productsBasic SQLDifficulty: beginnerReturn product_id, name and price for up to three products with a non-NULL price. Sort by price from highest to lowest, then product_id from lowest to highest to break ties. Use LIMIT 3.
- List unique customer citiesBasic SQLDifficulty: beginnerReturn one city column containing each distinct non-NULL city in customers. Keep an empty string if it exists. Row order does not matter.
- Count orders and total their valueIntermediate SQLDifficulty: beginnerReturn one row with total_orders (the number of orders, including rows with a NULL amount) and total_amount (the sum of non-NULL amounts). Return 0 for total_amount when there are no amounts to sum. Use these exact column aliases.
- Summarize sales by regionIntermediate SQLDifficulty: beginnerReturn region and SUM(amount) AS total_amount for every region represented in orders, including a NULL region. Do not filter by status. If a region has only NULL amounts, its total_amount must be NULL. Row order does not matter.
- Find regions above the sales targetIntermediate SQLDifficulty: intermediateGroup all orders by region. Return region, COUNT(*) AS order_count and SUM(amount) AS total_amount only for regions whose total_amount is strictly greater than 200. Count orders with NULL amounts too. Row order does not matter.
- Join orders to customer namesIntermediate SQLDifficulty: intermediateReturn order_id, customers.name AS customer_name and amount for every order with a matching customer_id in customers. Keep separate orders even when their customer names or amounts are identical. Row order does not matter.
- Find customers without ordersIntermediate SQLDifficulty: intermediateReturn customer_id and name for customers who have no matching order, regardless of order status or amount. An order with a NULL customer_id belongs to no customer. Row order does not matter.
- Find above-average customer spendingIntermediate SQLDifficulty: intermediateCalculate total_spent for every customer from all their orders, treating no orders or all-NULL amounts as 0. Return customer_id, name and total_spent only for customers whose total_spent is strictly above the average of these customer totals. Include zero-spend customers in that average. Row order does not matter. A subquery or CTE is fine.
Processing Data
- Load CSV RecordsPandas: Loading Data & EDADifficulty: beginnerRead csv_text with Pandas and return a list of row dictionaries in file order. The first line contains column names. Values are complete strings or finite numbers; let Pandas infer numeric types. Quoted commas and line breaks belong to their field. A header-only CSV returns an empty list. Use StringIO to read the supplied text; no file download is needed.
- Select DataFrame ColumnsDataFrame OperationsDifficulty: beginnerBuild a DataFrame from records and return only the columns named in columns, as a list of row dictionaries. Keep row order and the requested column order. Every requested column exists in each non-empty input row. Empty records return an empty list.
- Select Rows with loc and ilocDataFrame OperationsDifficulty: beginnerBuild a DataFrame from records using labels as its unique string index. Return a dictionary with labelled: the rows requested by selected_labels, in that order; and positioned: the rows in the positional slice start:stop. Return each set as a list of row dictionaries without the index. Requested labels exist. Positional slicing follows Python rules: stop is excluded and negative positions are allowed.
- Filter Scores by ThresholdDataFrame OperationsDifficulty: beginnerEach record has name and numeric score. Use a Pandas condition to return the names whose score is greater than or equal to minimum, in original row order. Include the boundary value and retain duplicate names. Empty input or no matches returns an empty list.
- Combine Product FiltersDataFrame OperationsDifficulty: beginnerEach record has product, category and numeric price. Return product names where category exactly matches wanted_category AND price is at most max_price. Keep original order and include the price boundary. Category matching is case-sensitive. Empty input or no matches returns an empty list.
- Sort a LeaderboardDataFrame OperationsDifficulty: beginnerEach record has name and numeric score. Return the names sorted by score from highest to lowest; break score ties by name in ascending, case-sensitive order. Keep duplicate rows. Empty input returns an empty list.
- Fill Missing ScoresData Cleaning with PandasDifficulty: beginnerEach record has name and score. A missing score is represented by None (null in the examples). Replace missing scores with replacement using Pandas and return all rows as a list of dictionaries with name and score. Preserve row order and existing scores, including zero. Empty input returns an empty list.
- Keep the First Email RecordData Cleaning with PandasDifficulty: beginnerEach record has name and email. Remove duplicate email addresses with Pandas, keeping the first record for each email in original row order. Compare email strings exactly, including case. Return a list of dictionaries with name and email. Empty input returns an empty list.
- Convert Text to NumbersData Cleaning with PandasDifficulty: beginnerConvert values into a Python list of numbers using Pandas. Inputs are decimal numeric strings, invalid text, empty strings or None. Numeric strings may contain surrounding whitespace or a minus sign. Replace invalid or missing values with 0. All valid numbers are finite. Keep order; empty input returns an empty list.
- Total Sales by RegionDataFrame OperationsDifficulty: intermediateEach record has region and numeric amount. Group by region with Pandas and sum the amounts, including negative refunds and zero sales. Return a list of dictionaries with region and total, sorted by region in ascending, case-sensitive order. Empty input returns an empty list.
- Merge Orders and CustomersDataFrame OperationsDifficulty: intermediateOrders contain order_id, customer_id and total. Customers contain unique customer_id values and name. Use a Pandas left merge to keep every order in its original order. Return dictionaries with order_id, customer (the matching name) and total; use "Unknown" for an unmatched customer. Several orders can share a customer. Either input can be empty; empty orders return an empty list.
- Extract Year and MonthDataFrame OperationsDifficulty: beginnerParse dates with Pandas. Each input is a valid date string in YYYY-MM-DD format, with no time or timezone. Return a list of dictionaries containing integer year and month for each date in original order. Retain duplicates. Empty input returns an empty list.