Skip to main content

Hobby coding project - Queries for play whe data

Image
By

I have an interest in open data and being able to query that data and gain beautiful insights. One data set that would be interesting is the play whe results data. Our open data is lacking in Trinidad and I will try to contact NLCB to see if they can provide and maintain the data online. But in the meanwhile I will use randomised data to create the website and do my testing.

First thing I did was install sqlite on termux

pkg update && pkg upgrade
pkg install sqlite
sqlite3 --version

Create my database in my project folder

sqlite3 results.db

Useful commands

.exit
.quit

Exit from multiline prompt

;

SQL to create my table (create_tbl_results.sql)

CREATE TABLE DrawResults (
    DrawNo INTEGER PRIMARY KEY,                           DrawDate DATE,
    ResultNo INTEGER CHECK (ResultNo BETWEEN 1 AND 36),
    DrawTime INTEGER CHECK (DrawTime BETWEEN 1 AND 4)
);

SQL to create the random data (random.sql)

WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 10000
)
INSERT INTO DrawResults (DrawNo, DrawDate, ResultNo, DrawTime)
SELECT
    n AS DrawNo,

    -- Start at 2010-01-01 and advance 1 day every 4 draws
    date('2010-01-01', '+' || ((n - 1) / 4) || ' days') AS DrawDate,

    -- Random number from 1 to 36
    (abs(random()) % 36) + 1 AS ResultNo,

    -- DrawTime cycles 1 to 4
    ((n - 1) % 4) + 1 AS DrawTime
FROM numbers;

SQL to query the top 10 called numbers (query_top10.sql)

SELECT
    ResultNo,
    COUNT(*) AS ResultCount
FROM DrawResults
GROUP BY ResultNo
ORDER BY ResultCount DESC
LIMIT 10;

Commands to run the sql (setup.sh)

rm results.db
sqlite3 results.db < create_tbl_results.sql
sqlite3 results.db < random.sql
sqlite3 results.db < query_top10.sql

Start the database from scratch in termux

chmod +x setup.sh
./setup.sh

Next up in next blog post is to create a website to display the results that will eventually be live on github pages

Update - website has been started here - https://hassan-theitguy.github.io/play-whe-queries/
Labels:

Comments

Post a Comment

Popular posts from this blog

Below the surface

By
This is a chapter from my eleventh book called Quotation Marks Sparks . __________ "You can't stop the waves, but you can learn to surf." - Jon Kabat-Zinn __________ I was looking at quotes that hit hard and this quote stood out to me. It seems mundane and obvious and often repeated. Life is about how we respond to the inevitable. It is not what happens to us but how we respond. I had to ask myself to dig deeper and look below the surface. Then I saw that when we surf we really become the waves. We become friends with the waves. The waves and us are the same. Further, the waves are created by us and in our heads. We can control the waves because who can surf all the time? Sometimes we just have to come to shore and build sand castles on shore. I asked my friend Gemini for feedback and he said that I was right, the Jon Kabat-Zinn quote initially seems like a simple call for resilience, but my deeper dive reveals profound layers. The idea that we can move beyond merely rea...
Post a Comment
Read more

A place for coders in Trinidad

By
DHub is being rebranded as CodeTT. Not sure how long before the change and launch takes place. Before you know it there will be elections again and then what if the government changes again. I am not optimistic about this. I want to be but I do not like politics. I wish society could function without politics. That aside it would be nice to have a home for coders in Trinidad and Tobago. I wonder what other countries are doing? I wonder if I will be alive if this ever happens. I think before any of this happens the government has to find a better way for citizens to communicate with them. From my experience I get the feeling that most people are just ignored. I have an idea for an app for this called GOQA - GOvernment Query App (QA also stands for Question and Answer). Every query (question, comment, suggestion, whatever) gets routed to the right person until it gets answered. Man that would be so fantastic. Finally a government that tries to listen to the people. My friend Chatty says ...
Post a Comment
Read more

No phrase has been said twice

By
It is 111am and I have decided to write. I have no topic, but I am thinking about God. I want God to help me write this blog post tonight. We are nearing the end of the year. Time waits on no one, they say. Life is short, they say. Make every moment count, they say. Tomorrow is not promised, they say. Is there anything they do not say? What is the least said phrase? What phrase has never been said before? Then I thought about something. The moon never shines the same way twice. Perhaps the same is true of phrases. Even if a phrase has been spoken a million times—or a billion times—every time it is spoken, it is different. Who says it matters. When they say it matters. The context in which they say it matters. Who is listening matters. And then there is what happens inside us. What do we hear? What thoughts pass through our minds as the words pass through us? Two people can hear the same sentence and receive two completely different messages. A phrase spoken in joy can become something ...
Post a Comment
Read more