SQL and data analysis course in Peru

import csv, sqlite3

FILE = "residuos_chiclayo_abril2026.csv"

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE r (fecha TEXT, mes TEXT, dia TEXT, turno TEXT, peso REAL)")
with open(FILE, encoding="latin-1") as f:
    for row in csv.DictReader(f):
        db.execute("INSERT INTO r VALUES (?, ?, ?, ?, ?)",
                   (row["FECHA_REGISTRO"], row["MES"], row["DIA"].strip(),
                    row["TURNO"], float(row["PESO"])))

def show(sql):
    for row in db.execute(sql):
        print(*row, sep=" | ")
    print()

show("""
SELECT substr(fecha, 1, 6) AS month, COUNT(*) AS shifts, ROUND(SUM(peso), 1) AS tonnes
FROM r
WHERE fecha BETWEEN '20240901' AND '20241231'
GROUP BY month
""")

Every day should have exactly three rows, one per shift, so the date is a key that ought to be unique per shift. GROUP BY with HAVING finds the days that break the rule. Then two columns that must agree are compared: the file's own month column against the month inside the date, and the weekday name against the weekday SQLite computes from the date.

show("""
SELECT fecha, COUNT(*) AS rows_that_day
FROM r
GROUP BY fecha
HAVING COUNT(*) <> 3
ORDER BY rows_that_day DESC
LIMIT 3
""")

show("""
SELECT mes, substr(fecha, 1, 6) AS month_in_date, COUNT(*)
FROM r
WHERE mes <> substr(fecha, 1, 6)
GROUP BY mes, month_in_date
""")

show("""
SELECT fecha, dia,
       strftime('%w', substr(fecha,1,4) || '-' || substr(fecha,5,2) || '-' || substr(fecha,7,2)) AS weekday_of_date
FROM r
WHERE mes = '202411'
LIMIT 1
""")

show("""
SELECT mes, COUNT(*) AS shifts, ROUND(SUM(peso), 1) AS tonnes
FROM r
WHERE mes BETWEEN '202409' AND '202412'
GROUP BY mes
""")

Credit: waste-collection register of the Municipalidad Provincial de Chiclayo, published on datosabiertos.gob.pe under ODC-By. We wrote and ran the queries, and computed the totals, on 9 November 2026 (Python 3, SQLite 3.50.4).

The story is now clear. The 90 rows whose month column says November 2024 carry October dates, 1 to 30 October. The weekday settles which column to trust: the first of those rows is dated 1 October 2024 but its weekday says VIERNES, Friday, while SQLite's strftime('%w') returns 2, a Tuesday. 1 November 2024 was a Friday. So the month column and the weekday are right, and the date was typed with the wrong month. Grouped by the month column, every month has 90 or 93 shifts and a believable total.

The first clash query found a second, stranger problem: 90 rows all dated 30 June 2025. Their weekday names run Sunday, Monday, Tuesday and so on in order, and 1 June 2025 was a Sunday, so the rows almost certainly cover the whole of June with one date pasted down the column. That one is left for the learner, below. Neither problem was visible in the dataset page, and neither needed anything more advanced than GROUP BY, HAVING and a comparison of two columns.

1. Rebuild June 2025. Assuming the rows are in date order, three per day, write a query or a short Python step that gives each one its real date. How would you check that your fix is right?
2. Count the gaps. Use a table of every calendar date from 1 January 2023 to 30 April 2026 and a LEFT JOIN to list the days with no rows. Which months are they in?
3. Clean the weekday. Make MIERCOLES and MIÉRCOLES the same value, then compare the average tonnes per shift on each weekday.
4. Write the note. In five sentences, tell the municipality what you found, with the query that proves each point.

Course lengths are from our own syllabuses. Our advice, not a ranking; the mentor confirms the choice at the free class.

The whole of Peru uses zone America/Lima, five hours behind UTC and without daylight saving since 1994; India uses UTC plus 5:30. That leaves a constant gap of ten and a half hours.

Chiclayo, Lima, Arequipa and Iquitos all share Peru time, so the slot you book works anywhere in the country. You choose in your own clock and the mentor sees the India time.