Oracle SQL Intersect

Intersect is incredibly useful for those who dislike using Excel to compare data sets. Quite simply, it will return rows from two matching selects that match.

If no match exists between the two, nothing will be returned. Perfect for use when you need to do list comparisons, especially where multiple columns exist.

SELECT offer_number FROM schema.table_1
INTERSECT
SELECT offer_number FROM schema.table_2

Oracle SQL Full Outer Join

The Full Outer Join will return the results of an inner join as well as a left and right join. This equates to all the records from Table A and Table B where a match exists between the two. If no match exists, the missing side will return a NULL value.

SELECT * FROM TableA 
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

Oracle SQL Subqueries

Subqueries are simply a query within a query.

In Oracle, subqueries can reside in the SELECT, FROM or WHERE clauses.

SELECT

SELECT tbls.owner, tbls.TABLE_NAME,
  (SELECT COUNT(column_name) AS total_columns
   FROM all_tab_columns cols
   WHERE cols.owner = tbls.owner
   AND cols.TABLE_NAME = tbls.TABLE_NAME) subquery2
FROM all_tables tbls;

FROM

 SELECT suppliers.name, subquery1.total_amt
FROM suppliers,
(SELECT supplier_id, SUM(orders.amount) AS total_amt
  FROM orders
  GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;

WHERE Clause

SELECT * FROM all_tables tabs
WHERE tabs.TABLE_NAME IN (SELECT cols.TABLE_NAME
                          FROM all_tab_columns cols
                          WHERE cols.column_name = 'SUPPLIER_ID');

Distributed Live Streaming

As much as I enjoy watching webcasts, the production value, despite the number of cameras and commentators, could be improved.

One possible solution could be to create a distributed live stream broadcasting platform, that allows users to sign-up to broadcast an event, stream with their own connections, allowing users to switch between different feeds of the same event.

This could incorporate the ASP feed, feeds from different angles and perspectives, as well as different audio feeds.

Technically it would be a challenge especially with consumer internet conncetivity and latency, however it would provide a unique experience for those watching online or at home – providing the ability to see the action live exactly how they want to see it.

Oracle SQL Inner Join

The join of two tables returning only matched rows is called an inner join. It produces only the set of records that match in both table a and table b

SELECT * FROM TableA
INNER JOIN TableB ON TableA.name = TableB.name

Fan and User Engagement in Competitive Surfing

All surfing events have created a black-hole in fan engagement under the current format, especially for online viewers.

Think of this recent scenario:

  • Wake up in morning awaiting the first heat of mens elimination round 2, heat between highly supported local Brenno Dorrington and reigning ASP world champ Joel Parkinson.
  • Open browser, hit refresh and wait for call.
  • Call is “LAYDAY”.
  • Feel disappointment.
  • Close browser and don’t bother checking event website until tomorrow morning.

The fan-engagement gap lies in the sporadic nature of the event schedule.

Understandably, fans don’t expect a contest to be ran in sub-par waves, and we all want high performances in high quality waves, but with the addition of alternate speciality divisions, in-depth profile pieces, news, pretty much anything, to fill in lay days, fans could continue watching the action.

Not only could other competition formats be used in the interim, but it’s obvious that on the lay-days the majority of the surfers are surfing anyway, so why not provide several dedicated ‘roaming’ camera/footage resources to follow highly influential surfers so their layday sessions can be captured and broadcasted. With 10-15 minutes notice via twitter or facebook, most users following the event would definitely tune in to a live broadcast of D-bah, Kirra or Snapper, if they know certain surfers will be in the water.

Three things that could fill the lay day gap could be:

Online/fan judged ‘MARZO’ format.
In-depth Q&A pieces with each WCT surfer, men & women.
Masters (40 – 50) or Legends (50+) division heats.
ProAM expression session heats.
Tag-team events with season long teams (either based on nationality, major sponsor, or chosen among surfers).

Heats on Demand in Competitive Surfing Events

Heats on Demand is a great feature, allowing users to re-live any heats they’ve missed in their entirety, with each notable event marked on a timeline with full commentary as shown when it was live.

However, it would be great for the ASP to release statistics on how users actually use the heats-on-demand.

My feeling is that any truly engaged user will have already seen the majority of the waves surfed throughout an event, and users looking at heats via on-demand would generally look at the top two scores of their favourite surfers only, two or three times, or even most likely just wave scores of 8 or above across all heats.

What does this mean?

It is a smart move to put all content online, so anyone who potentially complains about missing heats or waves has the power to go back and review. But based on my usage personally, I also think it shows the significance of professionally produced highlight reels. Other sports, including the NFL and NBA put these types of pieces together with such expertise, fans could watch them on repeat. They’re generally small, easily sharable via social networks and most significantly, fans will watch them no matter the duration – no jumping back and forth between waves and heats like when using heats-on-demand.

Could things be improved?

Absolutely. In regard to heats on demand, some analysis would at the very least provide some insights into user behaviour, which should lead to a better designed user-experience. In regard to highlight-reels, there is potential for them to be made interactive, but including interactive hot-triggers for users who are watching them, such as having a user tweet to ‘@jjf9.0quikpro’ to vote the best wave of the heat, round or competition. Companies could even hold promotional competitions via interactive social media just for those who are watching the highlight-reels, so they are still as engaged in those who watch the live broadcast.

triton io design company – technology and surfing integration

Triton Io Design Company is my web development and sports analytics company.

We are solely focused on creating innovation and user-engagement in the sport of surfing, accomplished with the use of technology.

We  aim to improve the surfing experience for amateur and professional free-surfers, competitors, clubs, event organisers and most importantly the fans, using web-applications, websites, online-services, mobile-marketing and responsive design.

We are also very keen to collaborate on projects related to surfing and tech.

Oracle SQL Left Outer Join

Left Outer Joins are commonly used when querying SQL databases. It is a join between two tables that returns the results of the inner join as well as the unmatched rows from the left table.

This will produce a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don’t want from the right side via a where clause (where TableB.id is null).

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name