Joins, Dataframes and a Read-Only Lock: My Week 6 at Dataraflow
How Intermediate SQL, NumPy and Pandas turned a live database into my playground this week.
Unexpected indeed — the server accepted the statement. That was the line I had always hoped never to come across. The query I had just written was intended to demonstrate a particular point, namely that my database account could not write to the server even if I tried, and for one heart-stopping moment I thought I had damaged something that I had never been allowed to touch.
I never had. The server responded precisely as it should have, and that brief moment of anxiety established the tone for my sixth week at Dataraflow. It was during this week that my work began to move beyond being confined to a notebook and started interacting with a real, shared database; I progressed from using basic SQL to employing joins, window functions, and running totals, and with the help of NumPy and Pandas I was able to bring the data into Python and actually make sense of it.
By the end of the week I had compared the weather in six cities across three continents, monitored a country’s milk exports month after month, and discovered a pattern in a loan dataset which was almost too neat to be true. In this article I explain what I found, the tasks that challenged me most, and the mistakes that taught me the importance of reading a traceback carefully before altering a single line of code.
What I Learnt During The Week
The week was built around three tools working together: SQL to pull the right data out of a live database, and NumPy and Pandas to shape and question it once it arrived.
NumPy
I practised the array checks that quietly sit underneath most data work: np.isnan() to catch missing values, np.iscomplex() and np.isreal() for number types, and np.allclose() and np.isclose() for comparing numbers that are “close enough” rather than exactly equal, which matters once decimals get involved. I also learnt that an array reports its own memory footprint through .size, .itemsize and .nbytes.
Intro to Pandas
A Pandas Series turned out to be the building block for everything else: I converted lists, dictionaries and NumPy arrays into Series, changed a Series’ data type with pd.to_numeric(), pulled a column out of a DataFrame as its own Series, and used .explode() to flatten a Series of lists into one long Series.
Intermediate SQL
This was the heart of the week. I moved well past a simple SELECT into querying INFORMATION_SCHEMA to explore a schema’s own structure, writing CASE statements for conditional counts, and remembering that SQL quietly rounds down whenever you divide two integers, so 100.0 instead of 100 in a calculation makes all the difference. I practised GROUP BY with HAVING to filter whole groups, JOIN and LEFT JOIN to combine tables and catch the rows that do not match, UNION ALL to stack similar tables into one, and window functions like ROW_NUMBER() OVER (PARTITION BY …) to rank rows within a group, such as finding the hottest day in each month.
I also learnt that connecting to a live database is not just about writing a correct query. My account was read-only, and the server was set up to reject anything beyond SELECT, no matter how the query was written.
The Tasks I Worked On
Five tasks from this week stood out to me the most, so here is how I approached each one.
Task 1: Finding the NULL and the Rain Hiding Inside Them
For London’s 2014 weather table, I had to count three things in one query: days with no recorded weather event, days whose event mentioned rain in any form, and days that had no recorded event but still showed rainfall. The tricky part was that the events column could not be compared with = NULL, since NULL never equals anything, including itself; IS NULL was the only way to catch it. I used CASE WHEN inside SUM() to turn each condition into a single count, and LIKE ’%Rain%’ to catch every version of a rainy event, from “Rain” to “Fog-Rain-Thunderstorm.”
SELECT
SUM(CASE WHEN [ Events] IS NULL THEN 1 ELSE 0 END) AS no_event_days,
SUM(CASE WHEN [ Events] LIKE '%Rain%' THEN 1 ELSE 0 END) AS rain_days,
SUM(CASE WHEN [ Events] IS NULL AND Precipitationmm > 0 THEN 1 ELSE 0 END) AS wet_but_no_event_days
FROM week06.london_2014;Task 2: Joining WHO and World Bank Data
This task combined WHO tuberculosis data with World Bank population figures on the country name, to calculate TB deaths per 100,000 people for the five worst-affected countries. I learnt how a simple JOIN can quietly drop rows the moment two tables spell a country’s name differently, which made me appreciate how much of real data work is just getting two sources to agree with each other before any calculation can happen.
SELECT TOP 5
t.Country,
ROUND(t.[TB deaths] * 100000.0 / w.[SP.POP.TOTL], 1) AS tb_deaths_per_100k
FROM week06.who_pop_tb_all AS t
JOIN week08.wb_pop_2013 AS w
ON t.Country = w.[Country]
ORDER BY tb_deaths_per_100k DESC;Task 3: The Hottest Day of Every Month
For this one, I needed the single hottest day in London for each month of 2014, with ties broken by the earlier date. I built a CTE to convert the date column properly, then used ROW_NUMBER() OVER (PARTITION BY MONTH(day) ORDER BY max_temp DESC, day ASC) to rank every day within its own month and keep only the top-ranked row. Window functions had felt abstract until this task made them click: they let you rank or compare rows without collapsing the table the way GROUP BY does.
WITH london AS (
SELECT
TRY_CONVERT(date, GMT) AS day,
[Max TemperatureC] AS max_temp
FROM week06.london_2014
),
ranked AS (
SELECT
MONTH(day) AS month_no,
day,
max_temp,
ROW_NUMBER() OVER (
PARTITION BY MONTH(day)
ORDER BY max_temp DESC, day ASC
) AS rn
FROM london
WHERE day IS NOT NULL
)
SELECT month_no, day, max_temp
FROM ranked
WHERE rn = 1
ORDER BY month_no;Task 4: A Business Question with a Red Flag
The Head of Credit wanted to know how loan approval rates varied by property area and credit history. I grouped by both fields and calculated an approval rate for each combination, but the result was the real lesson: every rural applicant with a bad credit history was rejected, and every semi-urban or urban applicant with a good credit history was approved, with no overlap at all. It was a reminder that a clean-looking query can still surface data too tidy to be trusted, and that spotting that is part of the job, not a distraction from it.
SELECT
Property_Area,
Credit_History,
COUNT(*) AS applications,
ROUND(100.0 * SUM(CASE WHEN Loan_Approved = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS approval_rate_pct
FROM week17.loan_approval_prediction
GROUP BY Property_Area, Credit_History
ORDER BY Property_Area, Credit_History;Task 5: Comparing Six Cities’ Weather with Pandas
Here I pulled a full year of weather data for Beijing, Brasília, Cape Town, Delhi, London and Moscow and compared them using Pandas. Brasília turned out to be the warmest city on average at 22.9°C and the most stable through the year, while Moscow was the coldest at 5.9°C with the widest swing in temperature. I also caught two data quality issues along the way: Moscow showed zero rainfall for an entire year, and Delhi’s temperatures looked more like a different, colder climate than the one it actually has.
Get Mubaraq Musa’s stories in your inbox
Join Medium for free to get updates from this writer.
Working against a live, shared database brought a different kind of challenge than anything I had faced before. These errors were not typos in my own code; they came from the server itself, and understanding them meant slowing down and reading every line instead of reacting to the first word in red.
A Connection That Refused to Reset Itself
Later, a query failed partway through, and every query I ran after that kept failing too, this time with a PendingRollbackError from SQLAlchemy. At first it looked like each new query had its own separate bug. Reading the traceback more closely showed the real story: one failed transaction had left the connection in a broken state, and SQLAlchemy was refusing to run anything else on it until that transaction was rolled back. Restarting the connection, and later wrapping my database calls so that a failure rolls back cleanly instead of leaving the connection stuck, solved it for good.
A Connection That Dropped Mid-Query
Partway through a query, the connection to the server cut out with a DB-Lib error: “Unexpected EOF from the server.” The traceback looked alarming at first, five stacked frames from deep inside pymssql, each one just saying it couldn’t show its own source code. Reading past them to the actual exception message showed the real story: the connection had simply gone stale, not a permissions or syntax problem at all. Closing it out and opening a fresh connection before retrying fixed it, and it taught me to look for the last line of a traceback first, not the longest one.
How I Got Through Them
In both cases, the fix was the same habit: stop, read the error message from the last line upward, and ask what it is actually describing before guessing at a fix. Once I understood that one error was expected behaviour and the other was a connection that needed a clean reset, the solution was quick. Rushing past the message was what had made both situations feel scarier than they really were.
CONCLUSION
Week 6 pushed me past querying for its own sake and into actually interrogating data: checking whether a join silently lost rows, questioning a result that looked too clean, and treating a database error as information rather than a disaster. NumPy and Pandas gave me the tools to carry that scrutiny from SQL straight into Python, and the two errors I ran into reminded me that reading carefully is still the most reliable debugging skill I have.
I am leaving this week more comfortable with joins, window functions and a live database connection than I expected to be seven days ago, and genuinely curious about what the next dataset will try to hide from me.
