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

Stuck running sftp server on termux alpine for multi-user setup

By
The below is my journey trying to get multi-user sftp working using alpine on termux pkg update && pkg upgrade pkg install proot-distro openssh proot-distro install alpine proot-distro login alpine apk update apk upgrade apk add openssh shadow sudo apk add vim vim /etc/ssh/sshd_config Port 8022 PermitRootLogin no PasswordAuthentication yes Subsystem sftp internal-sftp Match Group sftpusers     ChrootDirectory /sftp/%u     ForceCommand internal-sftp     X11Forwarding no     AllowTcpForwarding no addgroup sftpusers adduser user1 adduser user1 sftpusers mkdir -p /sftp/user1/upload chown root:root /sftp/user1 chmod 755 /sftp/user1 chown user1:sftpusers /sftp/user1/upload How I run my server each time pkill sshd rm -f /etc/ssh/ssh_host_* ssh-keygen -A /usr/sbin/sshd -D -d -d -d From another termux session sftp -P 8022 user1@127.0.0.1 Connection reset by 127.0.0.1 port 8022 Connection closed Some troubleshooting steps mkdir -p /run/sshd chmod 75...
Post a Comment
Read more

Recursion example with SQL

By
I asked my friend Chatty, can you use recursion in SQL? For example I have a table (ID, value) ordered by increasing ID. Can you guide me through using recursion to select all the rows of consecutive repeated values using and creating an SQLite database. Provide the small sample data. Is there a better way to do this? A More Modern Alternative (Window Functions) In real-world SQL, this problem is usually solved more efficiently using window functions like: LAG() ROW_NUMBER() SUM() OVER But recursion is excellent for learning traversal logic and hierarchical thinking. Recursion example using sqlite on termux DROP TABLE IF EXISTS sample; CREATE TABLE sample (     id INTEGER PRIMARY KEY,     value TEXT ); INSERT INTO sample (id, value) VALUES (1, 'A'), (2, 'A'), (3, 'A'), (4, 'B'), (5, 'B'), (6, 'C'), (7, 'A'), (8, 'A'), (9, 'D'), (10, 'D'), (11, 'D'), (12, 'D'); WITH RECURSIVE grouped AS (    ...
Post a Comment
Read more

Bless us today

By
It is 515pm and I have decided to write. Today is the day before the holiday and it feels like a Friday. I did not feel like doing anything this afternoon. But I got up and made dinner. Then brush my teeth and bathe. A round of applause to all of us for being alive and making it through the day. There are many things we can applaud ourselves for. There are many things we can applaud others for. It is good to pat ourselves (and others) on the shoulders now and again. Life is not easy. No one has it easy. Comfort is an illusion. But God is real and so we pray. It is 214pm on the following Monday and I have decided to continue my writing. I have this thought. God is holding my hand and we are walking. Walking in empty space. I closed my eyes and selected a verse from the holy Quran. I landed on surah 94 verse 5 - so verily with hardship there is ease. No matter how and when we are tested we must remind ourselves of the goodness of God. God is not going to leave us stranded and empty hande...
Post a Comment
Read more